October 12, 2001 at 11:14 pm
Here is one that does not make sense to me.
SELECT *
FROM tbl1
LEFT OUTER JOIN tbl2 ON tbl1.key = tbl2.key
WHERE tbl2.filter = 1
AND tbl2.key IS NULL
returns the same as
SELECT *
FROM tbl1
INNER JOIN tbl2 ON tbl1.key = tbl2.key
WHERE tbl2.filter = 1
However, the following works correctly
SELECT *
FROM tbl1
LEFT OUTER JOIN tbl2 ON tbl1.key = tbl2.key
AND tbl2.filter = 1
WHERE tbl2.key IS NULL
Is it me or shouldn't SQL flag a warning that a OUTER JOIN is being changed to a INNER JOIN by the optimizer?
October 13, 2001 at 5:32 am
As far as I know the SQL optimizer would not change a Outer Join to an Inner Join. It just so happens that you are filtering on Null which gives you the same results as if you had done a inner join. The reason the two queries have different results is because in the first one you are left outer joining all of the records from tbl2 in the second query you are only left outer joining the records that have a filter = 1
October 13, 2001 at 5:58 am
By the way, I ran your queries on simple data
Tbl1 Tbl2
Key Key Filter
1 1 1
2 2 0
3 3 1
4 4 0
And I get three different results:
First query returns empty record set
Second query returns
1 1 1
3 3 1
Third Query returns
2 NULL NULL
4 NULL NULL
October 13, 2001 at 8:24 am
Leon, you are correct. My appologies, I missed a AND clause on the second query.
It should have read:
SELECT *
FROM tbl1
INNER JOIN tbl2 ON tbl1.key = tbl2.key
WHERE tbl2.filter = 1
AND tbl2.key = NULL
My point is that the LEFT OUTER JOIN should have given me (by definition) every record in tbl1 and those records in tbl2 that match the criteria. Instead, it is only giving me those records that match in both tables. By the very definition of INNER/OUTER joins this is not correct behavior. Or is it?
Edited by - aplack on 10/13/2001 08:24:45 AM
October 13, 2001 at 8:39 am
The Query:
quote:
SELECT *FROM tbl1
INNER JOIN tbl2 ON tbl1.key = tbl2.key
WHERE tbl2.filter = 1
AND tbl2.key = NULL
will never return any records because tbl2.key can not be null by definition of an inner join. To get the understanding of inner and left outer joins do them without the Where clause and see what you get.
October 18, 2001 at 11:24 am
Your first query:
quote:
SELECT *FROM tbl1
LEFT OUTER JOIN tbl2 ON tbl1.key = tbl2.key
WHERE tbl2.filter = 1
AND tbl2.key IS NULL
If tb2.key is null, you didn't find a match in tbl2 for the record in tbl1 based on tbl1.key and tbl2.key. That means all the columns in tbl2 will be null. You've used WHERE tbl2.filter = 1. But tbl2.filter is null and so that part of the WHERE clause is always going to evaluate as false.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply