Understanding Outer Joins - 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)

Understanding Outer Joins

Author

Topic

obiwaugh
Starting Member

27 Posts

Posted - 2004-12-22 : 12:31:09

I'm trying to show all employees from a job history table, and then show their most recent pay rate change, if one exists. So the solution is of course an outer join.

When the outer join is coded in the FROM clause, it does not work:

SELECT JOB.EMPLID,JOB2.ACTION,JOB2.EFFDT
FROM PS_JOB JOB LEFT OUTER JOIN
PS_JOB JOB2 ON
(JOB.EMPLID = JOB2.EMPLID)
WHERE JOB.EFFDT = (SELECT MAX(B.EFFDT)
FROM DEMOV4.dbo.PS_JOB B
WHERE JOB.EMPLID = B.EMPLID)
AND JOB2.EFFDT = (SELECT MAX(C.EFFDT)
FROM DEMOV4.dbo.PS_JOB C
WHERE JOB2.EMPLID = C.EMPLID
AND C.ACTION = 'PAY')

But when the outer join is coded in the WHERE clause, it works fine:

SELECT JOB.EMPLID,JOB2.ACTION,JOB2.EFFDT
FROM PS_JOB JOB, PS_JOB JOB2
WHERE JOB.EFFDT = (SELECT MAX(B.EFFDT)
FROM DEMOV4.dbo.PS_JOB B
WHERE JOB.EMPLID = B.EMPLID)
AND JOB2.EFFDT = (SELECT MAX(C.EFFDT)
FROM DEMOV4.dbo.PS_JOB C
WHERE JOB2.EMPLID = C.EMPLID
AND C.ACTION = 'PAY')
AND JOB.EMPLID *= JOB2.EMPLID

What's going on

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

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-22 : 13:48:18

What does the phrase "it does not work" mean?

You are not really performing an outer join, regardless of the syntax. You are joining a table to itself based on the same key. There will always be a match so this is, in essence, an inner join.

The old style outer join (WHERE Clause) and the new style outer join (FROM Clause) are not equivalent.

HTH

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

Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910)

obiwaugh
Starting Member

27 Posts

Posted - 2004-12-22 : 14:03:01

If you look closely, I'm specifying a specific action 'PAY'. I want to see all EMPLIDs from JOB, but then only see date in the fields from JOB2 if the action is 'PAY'. In this case, there are many employees that have not had a pay rate change.

It seems like setting the EFFDT of JOB2 equal to a row with an action of 'PAY' excludes the employees that don't satisfy that criteria.

But when I code the outer join in the WHERE clause, this is not an issue. I'm trying to better understand why this is the case.

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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-22 : 14:05:42

The left outer join will include rows that do not have an entry in JOB2 - giving a null JOB2.EFFDT for them.
In the where clause you have
JOB2.EFFDT =
This will never be true for the nulls so your rows will be excluded.
You need to include this clause in the join or check for nulls to include them

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources