Skip Navigation LinksHome > View Post

Fun with SQL joins and predicates

The SqlHeads out there will probably laugh at this because it's so obvious. And with hindsight, it is, but I'm no SqlHead and this had me and a colleague (who is a SqlHead and shall therefore remain nameless) baffled for a little while.

So often in SQL, you get away with placing your predicates in either the JOIN or the WHERE part of the statement. For example, this...

SELECT * FROM Employee e
INNER JOIN Department d
    ON d.DepartmentID = e.DepartmentID
    AND d.DepartmentName = "Computing"

produces exactly the same results (and same execution plan) as this...

SELECT * FROM Employee e
INNER JOIN Department d
    ON d.DepartmentID = e.DepartmentID
WHERE d.DepartmentName = "Computing"

However, things get more complicated when you're playing with OUTER JOINs. As you know, outer joins populate the joined columns with NULLs if no match is found. The predication inside the JOIN statement is effectively applied before the join, whereas the predication in the WHERE statement is applied to the product of the join.

To see the difference this can make in action - here's some T-SQL to create two temporary tables in memory and populate them.

declare @tableA table (
    id int primary key)

declare @tableB table (
    id int primary key,
    ActiveFlag bit)

insert into @tableA values (1)
insert into @tableA values (2)
insert into @tableB values (1, 1)

The tables would look like this:

Table A Table B
ID
1
2
IDActiveFlag
11

If we only want records where the ActiveFlag = 1, do we mean before or after the join?


select * from @tableA a
    left outer join @tableB b
    on a.id = b.id
    and b.activeflag = 1

predicate within the join


select * from @tableA a
    left outer join @tableB b
    on a.id = b.id
where b.activeflag = 1

predicate within the where

Having just written this up, it looks kind of obvious. So much so that I'm tempted not to post it at all - but I figure I've written it now and it may prove of some use to somebody, and a laugh to the rest of you. I think I've never ran into this before because I tend to put each predicate where it 'makes sense', whereas this time I was troubleshooting an old query.

D'oh!

Tags: SQL

 
Josh Post By Josh Twist
8:31 AM
21 Jun 2006

» Next Post: SSIS Xmlify Data Flow Task
« Previous Post: Estimating coding horror

Comments are closed for this post.

Posted by Ian Horwill @ 22 Jun 2006 12:46 AM
You're right to post - you should post based on your initial reaction, not your after-the-fact omniscience, as that is where people will be who find your post helpful. What are the criteria for bona fide SQL Heads?

Posted by Josh @ 22 Jun 2006 12:57 AM
Thanks Ian, of course you're right and it was only pride that would have stopped me posting. We can't let something like that get in the way now can we!

Bona fide SQL Heads? You know who you are...

© 2005 - 2014 Josh Twist - All Rights Reserved.