LEFT OUTER JOIN vs. *= operator

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)

LEFT OUTER JOIN vs. *= operator

Author

Topic

mkilley
Starting Member

5 Posts

Posted - 2003-04-15 : 07:01:35

Can someone please help me see why these two queries differ in their results?


SELECT CD.* FROM ClientDetail CD, ClientAddress CA
WHERE CD.LastName LIKE 'BLOGGS%'
AND CD.ID *= CA.ClientID
AND CA.Address LIKE '%'

SELECT * FROM ClientDetail CD
LEFT OUTER JOIN ClientAddress CA ON CD.ID = CA.ClientID
WHERE CD.LastName LIKE 'BLOGGS%'
AND CA.Address LIKE '%'

.
Background - The record in ClientDetail that I am searching for does NOT have an associated record in ClientAddress. From the first query I get my client returned, yet from the second query - nothing!

If I remove the CA.Address LIKE '%' statement, then the second query works fine - as I would expect it to. Somehow it seems that when looking for a value on a specific field which is not being returned as part of the JOIN (as no match exists), then the OUTER JOIN rule is being broken and thus no records are being returned (i.e. the behaviour is more like an INNER JOIN).

Help!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-04-15 : 07:37:00

The behavior of LEFT JOIN is normal and is what it is SUPPOSED to return. The *= always had vague logical problems in its behavior that often brought back incorrect results, like what you're getting now.

If there is no match between the CD and CA tables, then using LIKE on the CA/right-hand side will always act like an inner join. The unmatched rows, being Null, cannot be compared using LIKE or any other comparison besides IS NULL. The fact that *= does return something proves that it is not working correctly.

The *= syntax is obsolete and should no longer be used. Books Online has more details about it, but ultimately SQL Server will no longer support it and you'll have to rewrite your queries to use LEFT JOIN anyway.

mkilley
Starting Member

5 Posts

Posted - 2003-04-15 : 09:26:12

Thanks for the info Rob.

As I had originally suspected then - the LEFT OUTER JOIN was working correctly in not returning any rows.

However - I'm not so sure about IS NULL working correctly... If I amend my query thus:

SELECT CD.* FROM ClientDetail CD
LEFT OUTER JOIN ClientAddress CA ON CD.ID = CA.ClientID
WHERE CD.LastName LIKE 'BLOGGS%'
AND CA.Address LIKE '%'
OR CA.Address IS NULL

Then I still don't get any rows returned and the query is still behaving like an INNER JOIN.

Do you have any ideas of how I may amend my query to operate correctly (in a single SELECT statement)? By operate correctly I mean return those rows that do match (a CA row exists and matches the LIKE statement) and those that don't (a CA row DOESN'T exist and the LIKE statement matches). To clarify further the query will be used in an stored proc and will look more along the lines of:

SELECT CD.*, CA.Address, CA.PostCode FROM ClientDetail CD
LEFT OUTER JOIN ClientAddress CA ON CD.ID = CA.ClientID
WHERE CD.LastName LIKE @Surname + '%'
AND CA.Address LIKE '%' + @Address + '%'
AND CA.PostCode LIKE '%' + @PostCode + '%'
OR CA.Address IS NULL

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-15 : 09:41:29

What you need to do is to add any search conditions for the right-side table (CA) as part of the JOIN like this:

SELECT CD.*, CA.Address, CA.PostCode FROM ClientDetail CD
LEFT OUTER JOIN ClientAddress CA ON CD.ID = CA.ClientID
AND CA.Address LIKE '%' + @Address + '%'
AND CA.PostCode LIKE '%' + @PostCode + '%'
WHERE CD.LastName LIKE @Surname + '%'

Note that the CA.Address and CA.PostCode filters have been moved to the LEFT JOIN and only the CD.LastName filter is used in the WHERE clause. rob has given a good explanation on why this works.

OS

mkilley
Starting Member

5 Posts

Posted - 2003-04-15 : 10:07:13

Brilliant - that works an absolute treat! I forgot completely that I could have added the extra filters to the JOIN clause.

I have now completed the aforementioned stored proc and it does exactly what it's intended to.

Cheers again for your help guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources