JOIN Syntax

Question

Post reply

JOIN Syntax

sho-467341

Ten Centuries

Points: 1062
More actions
July 18, 2008 at 7:56 am

#189780

Hi,
Can someone give me a few good reasons/arguments as to why you should use:
SELECT A.Col, B.Col
FROM TableA A
INNER JOIN TableB B ON A.Id = B.Id
Instead of...
SELECT A.Col, B.Col
FROM TableA A, TableB B
WHERE A.Id = B.Id
Cheers

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Inner Join is the ANSI standard and has been for a number of years.

The other way will not be supported in the future, so probably best not to begin using it.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

In addition to what Steve said, I also find it easier to read because join criteria is separated from any filtering criteria. Also when using Outer joins placement of the criteria can affect the results returned.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sho-467341 Ten Centuries Points: 1062 More actions · Answer 3

Thanks Steve and Jack.

Jack, do you have an example of the outer join problem?

Please keep posting if you have any more answers!! I want to knock this one on the head at work as I'm getting pretty bored of having to tell people to do this before every release!

Cheers

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

The INNER JOIN / OUTER JOIN syntax is ANSI 92 standard (yup - the old syntax is REALLY old). The old syntax is deprecated and will be completely unsupported in the next version of MS SQL Server after 2008.

In addition, the old outer join syntax (*= and =*) is already unsupported.

There have been a lot of threads on this lately, search this site.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

sho (7/18/2008)
Jack, do you have an example of the outer join problem?

This is not the best example, but does show a difference. Run this in AdventureWorks:

[font="Courier New"]SELECT

M.Title AS ManagerTitle,

E.Title AS EmployeeTitle

FROM

HumanResources.Employee M LEFT JOIN

HumanResources.Employee E ON

E.EmployeeID = M.ManagerID AND

E.Title LIKE '%Prod%'

ORDER BY

M.managerid

SELECT

M.Title AS ManagerTitle,

E.Title AS EmployeeTitle

FROM

HumanResources.Employee M LEFT JOIN

HumanResources.Employee E ON

E.EmployeeID = M.ManagerID

WHERE

E.Title LIKE '%Prod%'

ORDER BY

M.managerid

[/font]

The first query returns all the managers and only employees that have Prod in their title, the second returns only managers who have employees with Prod in their title.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question