Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Outer Join Trouble


Outer Join Trouble

Author
Message
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40542 Visits: 18846
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/outerjointrouble.asp

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
David.Poole
David.Poole
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4381 Visits: 3174
The behaviour of the outer join is very useful if you get an question such as

"Give me all the customers who ordered in December who didn't order in November"

You can do an INNER JOIN on the orders table to get the December figures and a LEFT JOIN on the ORDERS table and include the condition order_date BETWEEN '2005-11-01' AND '2005-11-30' in the join.

Although this way of doing things makes for a very easy to read query it isn't necessarily the most efficient way of achieving the desired result.

I found that in most situations it is actually more efficient to perform the LEFT JOIN on a derived table containing customers for November orders

LinkedIn Profile

Newbie on www.simple-talk.com
Lutz Albers
Lutz Albers
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 66

The behaviour posted in completly explainable. SQL-Server is working according to the SQL-Standards.

What most people don't know is that SQL "boolean" logic is actually a tripartite logic (a logic with three states) governed (simplified) by these additional rules:

  • state: true, false, undefined
  • a comparsion (==,<>,>,< ) with NULL is undefined (this is the reason for the IS NULL operator)
  • A and undefined is undefined
  • A or undefined is A
  • not undefined is undefined

A SELECT will deliver all rows where the WHERE clause evaluates to true.

A LEFT OUTER JOIN will match all rows from table A with all rows from table B for which the ON clause evaluatues to true. If there is no such match then it will deliver row A once with all SELECTed fields from B set to NULL.

If you apply this to the last example you can explain it:

select *
from a
left outer join b
on a.id = b.id
where b.id <> 8


This will deliver the following result before applying the WHERE clause:
id id
----------- -----------
1 NULL
2 2
3 NULL
4 4
5 NULL
6 6
7 NULL
8 8

The only rows where the WHERE clause evaluates to true are 2,4,6. Row 8 evaluates to false, all other to undefined.

select *
from a
left outer join b
on a.id = b.id
and b.id <> 8

id id
----------- -----------
1 NULL
2 2
3 NULL
4 4
5 NULL
6 6
7 NULL
8 NULL


This examples select all rows from A with rows from B which matching ID unequal to 8 (2,4,6) and selects one row from A with a NULL row from B.

One must be very careful when doing logic with NULL values.


Scott D. Smith
Scott D. Smith
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 18

I confess that I find the note by Lutz Albers much more comprehensible than the article about outer joins itself. I have found that doing everything inside of a stored procedure that begins like this ALWAYS solves these kinds of problems:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

The reason it works? Because setting ANSI NULLS ON gets rid of the three-way logic (true, false, and null). I have read numerous articles telling me that setting ANSI NULLS ON is totaly wrong. To this, I respectfully disagree: I program in the real world and don't have time for debates about set theory logic. Turning them off gets rid of these mysterious problems and that's good enough for me.


Bob Monahon
Bob Monahon
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 9

Hello,
Both the article and the comment by Lutz Albers were enlightening.

When I ran into this problem, I would solve it like this:

SELECT *
FROM a
LEFT OUTER JOIN
(SELECT * FROM b WHERE b.id <> 8) AS bx
ON a.id = bx.id

The above code clearly specifies (to me, anyway!) that excluding the value 8 should be applied to table b first, before the outer join is performed.

Thanks for the additional insight into this problem.




Regards,

Bob Monahon
jeff451
jeff451
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 219

I agree with the previous posts. The article and the comment from Lutz Albers certainly helped explain an issue I have dealt with many times.

Here is my quick and easy way of remembering how the where clause affects an outer join -

If the where clause contains a reference to the table from the outer join, then the join is in effect an inner join.

That may not be a great technical explanation but it works for me.




Jeff451
SQL Guru wannabe
BobAtDBS
BobAtDBS
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 361

Steve made one very good point in his article, which you don't want to lose as this thread drifts away from joins to nulls. The article was not about how boolean logic works with nulls, (which WAS explained quite nicely by Lutz).

A Where clause in an outer join is a filter, applied after the join takes place.

After that sinks in, all the other logic makes complete sense. It also explains why you sometimes see SQL Server not use an index where is "seems like it should" due to a field being mentioned in a where clause.




Student of SQL and Golf, Master of Neither
Canute Magalhaes
Canute Magalhaes
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 11

Hi Steve,

to your question on "how can you explain that those rows are removed? One other thing to notice is that this query matches up using an inner join (the first plan). " in your second example where b.id <> 8. I found a good article http://www.databasejournal.com/features/mssql/article.php/3399931 that says that " if your database or connection ANSI_NULL setting is set ON. An ANSI_NULL setting of ON is normally the default settings for databases and connections. When ANSI_NULL setting is set to ON, the ANSI SQL-92 standard states that any equal (=) or non equal (<> statement must equate to FALSE when compared against a NULL."

that could possible explain the reason for why the null <> 8 returns false.

Regards Canute.


Martin Vrieze
Martin Vrieze
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 125

Steve and all, nicely done!!!

I find the use of the additional "filter" in the ON clause facinating...

"...left outer join orders o
on c.customerid = o.customerid
and datepart(m, o.orderplacedate) = 10..."

I use A LOT of left outer joins in my work and this is the first time I've seen this type of qualifier embedded in the table join qualifier.



Antares686
Antares686
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: Moderators
Points: 8692 Visits: 780

Keep in mind the query is run thru the interpreter before it actually executes and many times the query is reformulated to the overall condition.

In you last examples the interpreter sees the fact the right table is referenced in the where clause despite it being a left join. Since the net condition is an inner join, that also would provide the fastest execution plan for the overall query, the query is optimized by the interpreter and actually executed as an inner join.

If you run

select *
from a
inner join b
on a.id = b.id
where b.id != 8

you will find you get exactly the same execution plan as

select *
from a
left outer join b
on a.id = b.id
where b.id != 8





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search