December 6, 2002 at 7:49 am
I have the following SQL...
SELECT A.*
FROM TABLE_1 A
LEFT OUTER JOIN TABLE_2 B
ON A.KEY = B.KEY
WHERE NOT(B.FLD_1 IN ('criteria'))
Why does this essentially result in an INNER JOIN between the tables?
I thought that the LEFT OUTER JOIN would pull back all information from Table_1 regardless of a match to Table_2 while excluding those records that did get a match to Table_2 where the criteria is true.
December 6, 2002 at 7:52 am
In order to prevent SQL from changing this to an INNER JOIN you need to specify the criteria in the JOIN and not the WHERE clause.
Try this.....
select a.*
from table_1 a
left outer join table_2 b on a.key = b.key and b.fld_1 in('criteria')
Edited by - paulhumphris on 12/06/2002 07:53:51 AM
Edited by - paulhumphris on 12/06/2002 07:54:30 AM
December 6, 2002 at 8:12 am
I'm guessing that the NOT IN logic would work the same way?
December 6, 2002 at 8:20 am
Yep
December 6, 2002 at 9:01 am
The following SQL definitely results in an INNER JOIN...
SELECT A.*
FROM TABLE_1 A LEFT OUTER JOIN TABLE_2 B
ON (A.KEY = B.KEY AND B.FLD_1 = 'criteria')
It is the same thing as using an INNER JOIN and then specifying the criteria in the where clause.
The following SQL which employs NOT logic does not return what you might expect...
SELECT A.*
FROM TABLE_1 A LEFT OUTER JOIN TABLE_2 B
ON (A.KEY = B.KEY AND B.FLD_1 <> 'criteria')
Returns all rows from Table_1 BUT DOES NOT EXCLUDE the entire row specified in the criteria, just the information from Table_2 for this row.
Can you confirm this information?
December 6, 2002 at 9:42 am
I reckon that
SELECT A.*
FROM TABLE_1 A LEFT OUTER JOIN TABLE_2 B
ON (A.KEY = B.KEY AND B.FLD_1 = 'criteria')
is an outer join and as you say will return all rows in A.
It is not the same as an inner join which will only return rows in A where there is matching columns in B.
If you add WHERE B.KEY IS NOT NULL to the above then it will produce the same as
SELECT A.*
FROM TABLE_1 A INNER JOIN TABLE_2 B
ON (A.KEY = B.KEY AND B.FLD_1 = 'criteria')
and
SELECT A.*
FROM TABLE_1 A INNER JOIN TABLE_2 B
ON A.KEY = B.KEY
WHERE B.FLD_1 = 'criteria'
Edited by - DavidBurrows on 12/06/2002 09:42:46 AM
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply