Is it possible to make Outer join with old style code

Question

Post reply

Is it possible to make Outer join with old style code

mario17

SSCertifiable

Points: 6078
More actions
June 25, 2013 at 11:08 pm

#295727

Hi,
I'm dealing with old style code that do joins without JOIN keyword like in a piece below, in such case is it possible somehow to implement OUTER functionality ?
select t1.c1, t1.c2, T2.COL3 from t1, T2
where t1.c1 = T2.Col1

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

The SQL89 standard for outer joins is *= or =* depending of you want a left or right outer join.

eg.

select t1.c1, t1.c2, T2.COL3 from t1, T2

where t1.c1 =* T2.Col1

HanShi SSC-Dedicated Points: 33506 More actions · Answer 2

Above sample is equal to an INNER JOIN. To get the FULL OUTER JOIN you'll just need to remove the WHERE clause. To get a LEFT (or RIGHT) OUTER JOIN change the WHERE clause to "WHERE columnname IS NOT NULL).

But since you are rewriting the query it is much better (readability, following standard) to user the JOIN syntax.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Andrew G SSChampion Points: 12809 More actions · Answer 3

That is incorrect, if you remove the WHERE clause, you're getting a cartisian product or cross join (every record from left joined with every combination from the right). A full outer join will return all rows from both tables, big difference.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 4

Andrew G (6/26/2013)
if you remove the WHERE clause, you're getting a cartisian product or cross join

Of course! You're right... and I will get some coffee to wake up 😉

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Andrew G SSChampion Points: 12809 More actions · Answer 5

Caffeine is good 😀

But I do agree, use the SQL92 (OUTER JOIN) standard for joins, it's supported in SQL2000 and higher, so can't see any reason to use the old code.

mario17 SSCertifiable Points: 6078 More actions · Answer 6

Thanks, I'll try =+

Forgot to tell that i'm on MS SQL Server

Tx

M