From:
Sent: Monday, October 19, 2009 3:32 PM
To:
Subject: RE: SQL question
Happy to help. Thanks for the hyperbole. :)
There is no functional difference, however, the first method – using a “comma join” is undesirable for a few reasons.
1) Using “comma joins” is old code and only supported at the pleasure of Microsoft. Its considered bad syntax. Its considered lazy. JOINS are the standard for TSQL. It may not be supported in future versions of SQL server.
2) It mixes your JOIN logic with your SELECTION logic. Imagine you added WHERE EMPID = ‘10’ to the sample query. Both functionally and in business logic, the EMPID = ‘10’ and the A.EMPLID = C.EMPLID are two very different statements. Functionally, it will probably work (not 100%), yet it is far more difficult to understand and maintain.
3) It is really difficult to read. Imagine a much larger query that has a bunch of commas in the FROM and then a tangle of statements in the WHERE Clause. Using JOINs and ONs are far easier to read for the user that comes behind you.
4) Screw up your WHERE clause and you’ll end up with a Cartesian product. Row count explosion.
Attached is a lab to demonstrate the various types of joins from a functionality standpoint.
From:
Sent: Monday, October 19, 2009 3:23 PM
To:
Subject: SQL question
Hey man, I have a SQL question for you.
What is the difference between this:
FROM A, C
WHERE A.EMPLID = C.EMPLID
And this:
FROM A
INNER JOIN C on A.EMPLID = C.EMPLID
And the benefit of one over the other?
Hope all is well with the “Grand SQL Wizard”!!!
(Yeah, I'm not sure either if that last bit was sarcasm or hyperbole either. It certainly wasn't genuine or factual. :) )
Here's that handy .sql script I keep around for demonstrating basic JOIN principles and syntax. Note that all of the syntax in this script is valid, but a lot of it is unacceptable and improper - that is sort of the point. This lab usually goes along with a short, stern lecture :) (see above) on why proper JOIN syntax is preferable.
DECLARE @T1 TABLE"Programmers can be lazy." -Larry Wall
(
tkey int
)
DECLARE @T2 TABLE
(
tkey int
)
INSERT INTO @t1 VALUES (1)
INSERT INTO @t1 VALUES (2)
INSERT INTO @t1 VALUES (3)
INSERT INTO @t2 VALUES (3)
INSERT INTO @t2 VALUES (4)
INSERT INTO @t2 VALUES (5)
SELECT * FROM @T1 T1 INNER JOIN @T2 T2 ON T1.tkey = T2.tkey
SELECT * FROM @T1 T1 JOIN @T2 T2 ON T1.tkey = T2.tkey
SELECT * FROM @T1 T1 LEFT OUTER JOIN @T2 T2 ON T1.tkey = T2.tkey
SELECT * FROM @T1 T1 RIGHT OUTER JOIN @T2 T2 ON T1.tkey = T2.tkey
SELECT * FROM @T1 T1 CROSS JOIN @T2 T2
SELECT * FROM @T1 T1 FULL OUTER JOIN @T2 T2 ON T1.tkey = T2.tkey
SELECT * FROM @T1 T1 , @T2 T2
SELECT * FROM @T1 T1 , @T2 T2 WHERE T1.tkey = T2.tkey
