Filter In Left Outer JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Filter In Left Outer JOIN

Author

Topic

aspnet100
Starting Member

1 Post

Posted - 2005-06-11 : 09:48:53

How to interpret the result from this query( the filter: AND E.deptno IS NULL in ON confuses me):

Result:
Deptno deptname empid empname deptno jobid salary
----------- --------------- ----------- ---------- ----------- ----------- ---------
100 Engineering NULL NULL NULL NULL NULL
200 Production NULL NULL NULL NULL NULL
300 Sanitation NULL NULL NULL NULL NULL
400 Management NULL NULL NULL NULL NULL

Query:
SELECT
* FROM
Departments AS D
LEFT OUTER JOIN
Employees AS E ON D.deptno = E.deptno
AND E.deptno IS NULL
Tables:
Deptno deptname
----------- ---------------
100 Engineering
200 Production
300 Sanitation
400 Management
==================
empid empname deptno jobid salary
----------- ---------- ----------- ----------- ---------
1 Leo 400 30 10000.00
2 George 200 20 1000.00
3 Chris 100 10 2000.00
4 Rob 400 30 3000.00
5 Laura 400 30 3000.00
6 Jeffrey NULL 30 5000.00

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-11 : 10:34:16

By specifying that a column in the WHERE clause as NULL from the outer table (used in an OUTER JOIN, d.deptno = e.deptno), you're asking for records from your inner table that don't exist in the outer table.

However in your query, specifying the join column as null in the JOIN criteria, makes no sense. Your basically joining only on non-existant values. So the results will be all rows from inner table and all nulls for returned columns from the outer table.

this illustrates the point:


set nocount on
declare @tbI table (c int)
declare @tbO table (c int)

insert @tbI values (1)
insert @tbI values (2)
insert @tbO values (2)
insert @tbO values (3)

select	o.c as o, i.c as i
from	@tbI i
left join @tbO o 
		on i.c = o.c 
		and o.c is null

select	o.c as o, i.c as i
from	@tbI i
left join @tbO o 
		on i.c = o.c 
where	o.c is null

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-11 : 17:16:50

The problem is that the join condition can never be true, unless you have ANSI_NULLS OFF. With ANSI_NULLS ON Two NULLS do not equal each other, so even if D.deptno and E.deptno are both null, the condition is not true. It is usually not a good ides to turn off ANSI_NULLS without understanding exactly what it means. You should read the topic "NULL Comparison Search Conditions" in SQL Server Books Online.

This shows how it works:


set ansi_nulls on

if NULL = NULL
	begin print '= TRUE with ansi_nulls on' end
else
	begin print '= Not TRUE with ansi_nulls on' end

if NULL <> NULL
	begin print '<> TRUE with ansi_nulls on' end
else
	begin print '<> Not TRUE with ansi_nulls on' end

set ansi_nulls off

if NULL = NULL
	begin print '= TRUE with ansi_nulls off' end
else
	begin print '= Not TRUE with ansi_nulls off' end

if NULL <> NULL
	begin print '<> TRUE with ansi_nulls off' end
else
	begin print '<> Not TRUE with ansi_nulls off' end

Results:
= Not TRUE with ansi_nulls on
<> Not TRUE with ansi_nulls on
= TRUE with ansi_nulls off
<> Not TRUE with ansi_nulls off

quote:
Originally posted by aspnet100
SELECT
* FROM
Departments AS D
LEFT OUTER JOIN
Employees AS E ON
D.deptno = E.deptno AND E.deptno IS NULL

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources