OK....what's the deal - 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)

OK....what's the deal

Author

Topic

obiwaugh
Starting Member

27 Posts

Posted - 2005-01-12 : 14:10:10

In the first SELECT statement, the outer join is killed by the selection criteria for the table on the right side of the join.

In the second SELECT statement, even with the same selection criteria, the outer join is preserved because it's in the WHERE clause.

Why is that?

SELECT P.EMPLID,P.NAME,EMP.JOBCODE,EMP.DEPTID
FROM PS_PERSONAL_DATA P LEFT JOIN PS_EMPLOYEES EMP
ON P.EMPLID = EMP.EMPLID
WHERE EMP.DEPTID = 222002

SELECT P.EMPLID,P.NAME,EMP.JOBCODE,EMP.DEPTID
FROM PS_PERSONAL_DATA P, PS_EMPLOYEES EMP
WHERE P.EMPLID *= EMP.EMPLID
AND EMP.DEPTID = 222002

I know enough to know that I don't know enough.

TimS
Posting Yak Master

198 Posts

Posted - 2005-01-12 : 15:47:57

Try the following, it may help you understand that when EMP.DEPTID IS NULL it does NOT equal 222002; that is why the first query of yours does what it does.

SELECT P.EMPLID,P.NAME,EMP.JOBCODE,EMP.DEPTID
FROM PS_PERSONAL_DATA P LEFT JOIN PS_EMPLOYEES EMP
ON P.EMPLID = EMP.EMPLID AND EMP.DEPTID = 222002

OR

SELECT P.EMPLID,P.NAME,EMP.JOBCODE,EMP.DEPTID
FROM PS_PERSONAL_DATA P LEFT JOIN PS_EMPLOYEES EMP
ON P.EMPLID = EMP.EMPLID
WHERE EMP.DEPTID IS NULL OR EMP.DEPTID = 222002

Tim S

obiwaugh
Starting Member

27 Posts

Posted - 2005-01-12 : 16:02:39

I understand the need for the NULL criteria in the example where the join occurs in the FROM clause.....but why do I not need the NULL criteria when the join occurs in the WHERE clause.

That's what I'm trying to understand. You don't need to account for NULLs when the join occurs in the WHERE clause. Why?

I know enough to know that I don't know enough.

BCrowe
Starting Member

23 Posts

Posted - 2005-01-12 : 16:11:51

Use Query Analyzer to run the following and I think you'll get your answer:

SELECT Count(*) FROM PS_PERSONAL_DATA
SELECT Count(*) FROM PS_EMPLOYEES_EMP
SELECT Count(*) FORM PS_PERSONAL_DATA, PS_EMPLOYEES_EMP

If you don't see the relation, reply and i or someone else will explain

BCrowe

obiwaugh
Starting Member

27 Posts

Posted - 2005-01-12 : 16:21:18

I assume that you want to demonstrate that there are more people in PS_PERSONAL_DATA than in PS_EMPLOYEES. So when you do a left outer join, I should see everyone in PS_PERSONAL_DATA whether or not they're in PS_EMPLOYEES. That's not the issue I'm trying to resolve.

I'm trying to understand why having selection criteria on PS_EMPLOYEES (the 'right' table) will kill the outer join (making it an inner join) if the join is coded in the FROM clause. But if the outer join is coded in the WHERE clause, selection criteria on PS_EMPLOYEES does not kill the outer join.

I know enough to know that I don't know enough.

obiwaugh
Starting Member

27 Posts

Posted - 2005-01-12 : 16:34:22

I found the answer...and it's very interesting.

http://www.microsoft.com/sql/techinfo/tips/development/July23.asp

I know enough to know that I don't know enough.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-12 : 16:54:03

The old style outer join syntax is inherently ambiguous. That is why the new style was brought into being. It's also why the two syntaxes do not yield the same recordset.

HTH

=================================================================

Scriptures, n. The sacred books of our holy religion, as distinguished from the false and profane writings on which all other faiths are based.
-Ambrose Bierce, writer (1842-1914) [The Devil's Dictionary]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources