Re: What should the following query return

Shubhasheesh Anand <anand@jwala.engr.sgi.com> wrote in article <3396F0A3.167E@jwala.engr.sgi.com>... > Hi, > > I have a query that does not seem to behave the way I > think it should !! > > Please see if you can help in pointing out the problem. > > I have the following schema: > > CREATE TABLE tb1 > ( > license_no INT NOT NULL, > name VARCHAR(250) NOT NULL > );> > CREATE TABLE tb2 > ( > license_no INT NOT NULL, > country VARCHAR(100) NOT NULL, > state VARCHAR(100) NOT NULL > );> > > And I insert the following data: > > INSERT INTO tb1 values ( 1, 'Hen' );> > SELECT t1.license_no > from tb1 t1 , tb2 t2 > where t1.name = 'Hen' or ( t1.license_no = t2.license_no and t2.country> = > 'Mars' and t2.state = 'Colorado' ) > > The above query does not return anything, though I think it > should return the row in tb1. > > Subsequently, if I insert the following: > > INSERT INTO tb2 values (3, 'Jupiter', 'Kansas');> > The same query DOES correctly return the row: > > license_no > 1 > > Now if I insert the following : > INSERT INTO tb2 values (4, 'Saturn', 'Texas' );> > and run the same query, I get the same row, but twice: > > license_no > 1 > 1 > > Though I think it should return it only once. > > Thanks > Shubhasheesh Relational Databases 101: 'Natural' table joins are cross products limited by the conditions in the where clause. If one of the tables is empty, the cross product is emtpy! 'Outer' joins can be used to give you all of the rows from one table, even when there are no rows in the other table which match your where clause. Since the "join condition" in your where clause is OR'd to the rest of the expression, you effectively have no condition to limit the cross product and therefore you get two rows. Thus you get two rows in your second example.