Outer Join Trouble
-
I also want to answer John Kelly:
1 .This is a good article
2. This site IS for both Newbies and professional DBAs.
3. Professional DBAs do not write code every day, they do server administration. It is Database Developers who write code
4. John, mind if I ask you what is WMIC (do not look up the answer on the web). Also, can you tell me what does KB867460 does to your database application if installed on the server? These are the issues most of DBAs (database administrators) have to deal with every day. But I would not risk posting on this site that an article on WMIC is below the professional level because the info about it is posted on Microsoft website.
Regards,Yelena Varsha
-
Lutz was correct is saying that the behaviour of the examples is caused by the behaviour of sql predicates involving nulls rather than outer join, as was his description of outer join. Likewise, Bob's observation that the WHERE clause is always *conceptually* applied after the evaluation of the FROM clause (and to each row returned by the FROM clause).
IMHO, understanding both these concepts is fundamental to understanding the way in which sql works. The latter (order of eveluation) is particularly important when understanding any sql statement with a WHERE clause contains nested subqueries.
In Steve's original example, where he wanted rows with a null orderplacedate, the correct thing to have done, as pointed out by Yelena was to write the WHERE clause as:
WHERE datepart(m, o.orderplacedate) = 10 or o.orderplacedate is NULL
Returning to Lutz's explanation of (left) outer join, for those that speak sql better than english:
select A.id, B.id
from A left outer join B
on a.id = b.id and b.id <> 8
is the same as:
select A.id, B.id from A inner join B on a.id = b.id and b.id <> 8
UNION ALL
select A1.id, Null from A A1
where not exists( select A.id, B.id
from A inner join B on a.id = b.id
and b.id <> 8
where A.id = A1.id)
Of course, before the '92 sql standard introduced outer join, we always had to resort to doing it the long way.
Regards
Ed Dee
-
Ed,
Thanks. It is me who always says that my SQL is better than my English. And as targeted audience I read your post.
Another exaple of the "long way" based on your example:
select A.id, B.id from A inner join B on a.id = b.id and b.id <> 8
UNION ALL
select A1.id, Null from A A1
where A.id not in ( select A.id
from A inner join B on a.id = b.id
and b.id <> 8
where A.id = A1.id)
But there is a catch: In the first line
select A.id, B.id from A inner join B on a.id = b.id and b.id <> 8
there will be duplicate entries for the customers, if we are talking about Customers and Orders like Steve started because one customer may have multiple orders. I tested it. You need to use Distinct keyword in this line and not list orders in the select statement to list only customers, not customer-order combination.
Regards,Yelena Varsha
-
Well, for my two penneth worth I thought the article was very useful as well as all the subsequent replies.
However, Ed stated that the correct "where" clause should have been :
WHERE datepart(m, o.orderplacedate) = 10 or o.orderplacedate is NULL
I believe this is incorrect, as this will not return what we want. It will only return 3 records.
'Andy' will not be returned, since there are records for that customer (the = 10 part of the where clause is ignored). Delaney is returned since there are no orders at all for them. If they happened to have any other orders in the system, we would still end up with only 2 records returned.
-
Thanks. This explains some performance problems we were having.
-
or, simply:
select a.*, b.id
from a left outer join b on a.id = b.id
where (b.id 8 or b.id is null)
It's more a matter of knowing how to deal with NULLs rather than any tricky stuff regarding outer joins.
I thought the article's author was going to refer to some of the more interesting outer join dilemmas that the likes of Joe Celko puts in his books...
-
Hello Steve Hirsch - I also traverse between DB worlds. Here are SQL Server equivalents to your Oracle query; for this simple a query they work the same.
SELECT a.field, b.field
FROM table1 a, table2 b
WHERE a.id *= b.id
SELECT a.field, b.field
FROM table1 a LEFT OUTER JOIN table2 b
ON a.id = b.id
FYI, the first notation goes back to Sybase days, and is still supported in SQL Server; I believe the second notation is now preferred by the powers that be.
Regards,Bob Monahon
-
Thanks Bob...oy, more syntax to learn. If the old way works, let's keep it!
-
i don't see where the problem arises, you are using a qualifier which by definition limits the results... i am confused by your confusion
-
This is straight from BOL:
Specifying Joins in FROM or WHERE Clauses
The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.
Outer join conditions, however, may interact differently with the WHERE clause search conditions, depending on whether the join conditions are in the FROM or WHERE clause. Therefore, the ability to specify Transact-SQL outer joins in the WHERE clause is not recommended, is no longer documented, and will be dropped in a future release.
Search for the heading in BOL for the full, albeit brief article.
Dave J
-
I often fall for this kind of problem too! It is one of the reasons I am now much more cautious with outer joins. I now also try much harder not to have nullable attributes in my tables either.
Whenever I have an outer join and the non-preserved table appears anywhere in the restrictions I use ISNULL wherever I can, for example:
instead of
select *
from a
left outer join b
on a.id = b.id
where b.id <> 8 -- implies b.id is not NULL
I use
select *
from a
left outer join b
on a.id = b.id
where ISNULL(b.id, [some harmless value here, often 0]) <> 8
This is not ideal, but whenever there is a good "harmless" value available, it usually does the trick. I find it makes long queries a little more readable than using "OR b.id IS NULL"
-
If the Database compatibility level is 80 or less the following query returns the same output
select
* from a,b where a.id *= b.id and b.id <> 8
as returned by the following
select
* from a left outer join b on a.id = b.id and b.id <> 8
bm21 -
I often have to return results requiring filtering on both tables AND a left outer join.
My answer is to use a nested subquery for the fields from the right hand table.
The following works beautifully. You have to remember that the reference to table1 in the outer query is not the same as that to table 1 in the inner query, hence one is aliased as t whislt the other isn't.
So if Table 1 =
ID int,
Code varchar(10),
Link int
and Table 2 =
ID2 int,
Link_ID int, (foreign key to table 1)
Link_Descvarchar(10),
Type int
select all rows from table 1 with code = A and rows from table 2 which match and have type = 1 becomes
select t.ID, t.Code, t.Link,
(select ID2 from Table1 INNER JOIN
table2 ON table1.ID = table2.id2
WHERE (table1.code= 'A')
AND (t.ID = table1.ID)
AND (table2.type= 1)) AS SecondID
FROM Table1 t LEFT OUTER JOIN
Table2 ON t.ID = table2.id2
WHERE (e.code= 'A')
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply