criteria in JOIN clause or WHERE clause

As an Access developer, I've written a lot of queries over the last 20 years.  In the past several years, I've been using SQL Server more extensively and continue to see syntax that I observed many years ago:

SELECT A.Field1, A.Field2, B.Field3
FROM TableA as A, TableB as B
WHERE TableA.PKField = TableB.FKField

instead of using a join clause to join the two tables.  Of late, I've started to see syntax similar to:

SELECT A.Field1, A.Field2, B.Field3
FROM TableA as A, TableB as B
ON TableA.PKField = TableB.FKField AND TableA.Field1 = 3

Is there an advantage of this syntax over the more traditional:

SELECT A.Field1, A.Field2, B.Field3
FROM TableA as A, TableB as B
ON TableA.PKField = TableB.FKField
WHERE TableA.Field1 = 3
LVL 52
Dale FyeOwner, Developing Solutions LLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
All these are accepted syntaxes and is an old style for sql queries..It is mentioned as comma syntax and it was replaced around 1992
ste5anSenior DeveloperCommented:
The last one isn't valid SQL or T-SQL as far as I know.

It's either ..FROM tableList WHERE joinConditions or ..FROM tableName (JOIN tableName ON joinCondition)*.

Both valid syntax schemes produce normally the same query plan. The JOIN syntax is imho better readable, cause it provides some information about the logical model.
PortletPaulEE Topic AdvisorCommented:
SELECT A.Field1, A.Field2, B.Field3
FROM TableA as A, TableB as B
WHERE TableA.PKField = TableB.FKField
Thats is "traditional" where the join logic is held entirely within the where clause.

SELECT A.Field1, A.Field2, B.Field3
FROM TableA as A, TableB as B
ON TableA.PKField = TableB.FKField AND TableA.Field1 = 3
That is a horrible hybrid, not following any standard and it probably would not work


SELECT A.Field1, A.Field2, B.Field3
FROM TableA as A
INNER JOIN TableB as B ON TableA.PKField = TableB.FKField
WHERE TableA.Field1 = 3
I would expect the query to appear like that above. Using the stated INNER JOIN followed by the join predicate(s) and then the where clause which contains NO join logic at all.

{+ edit}
The biggest difference between  "normal" Access join syntax and SQL Server, that I am aware of, is that Access requires/desires many parentheses that SQL Server simply ignores (and does not need).

e.g.
in Access this is common:

       FROM (a INNER JOIN b on a.id = b.id)

in MSSQL the same would be:

       FROM a INNER JOIN b on a.id = b.id

you could use the Access parentheses but they are redundant

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Paul,

That's what I get for copying and then modifying.  The syntax I was attempting to compare and get an analysis of is:

SELECT A.Field1, A.Field2, B.Field3
FROM TableA as A
INNER JOIN TableB as B ON TableA.PKField = TableB.FKField AND TableA.Field1 = 3

versus

SELECT A.Field1, A.Field2, B.Field3
FROM TableA as A
INNER JOIN TableB as B ON TableA.PKField = TableB.FKField
WHERE TableA.Field1 = 3

I have seen the previous method several times in the last couple of months and was wondering:
1.  When that syntax became feasible
2.  Whether it achieves any savings in the query plan over the more common INNER JOIN / WHERE construct

I've really only started looking at query plans recently, but wouldn't it be more efficient to create a subquery before the join:

SELECT A.Field1, A.Field2, B.Field3
FROM (
SELECT * FROM TableA
WHERE TableA.Field1 = 3
) as A
INNER JOIN TableB as B ON TableA.PKField = TableB.FKField

Thus significantly reducing the number of records which must be joined?
Scott PletcherSenior DBACommented:
1) That's always been valid.  I'm not sure what anyone's issue with it is, and it will absolutely work.  For INNER/equi joins, joins in the WHERE clause are unambiguous so it's just a matter of ease of understanding, not accuracy or reliability.

2) Currently I believe SQL would generate the same plan for either one.  At one time, it's possible the first version would have generated a more efficient query plan.
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Thanks, guys.  There was a big gap between my use of SQL Server 2000 and my current foray into 2008 R2 and I'm really trying to brush up on T-SQL best practices.
ste5anSenior DeveloperCommented:
Hmm, there is a difference when using OUTER JOINS:

SELECT  A.Field1 ,
        A.Field2 ,
        B.Field3
FROM    TableA AS A
        LEFT JOIN TableB AS B ON TableA.PKField = TableB.FKField
                                  AND TableB.Field1 = 3;

--versus

SELECT  A.Field1 ,
        A.Field2 ,
        B.Field3
FROM    TableA AS A
        LEFT JOIN TableB AS B ON TableA.PKField = TableB.FKField
WHERE   TableB.Field1 = 3;

Open in new window



The second condition on the outer table means in the first case: Only retrieve rows from B with that condition, but all from A.

The second case is different. It takes first all rows from A and the matching rows from B. Then the condition is applied to this result set. Cause it is a value comparison, now all rows which don't match are excluded. Thus all rows where B has no row will be removed, cause they contain NULL. And NULL = const is not true, thus all these rows are affected and removed.

E.g.

DECLARE @A TABLE ( ID INT );
DECLARE @B TABLE ( ID INT, Value INT );

INSERT  INTO @A
VALUES  ( 1 ),
        ( 2 ),
        ( 3 ),
        ( 4 ),
        ( 5 );

INSERT  INTO @B
VALUES  ( 1, 1 ),
        ( 2, 2 ),
        ( 3, 3 );

-- Returns 5 rows
SELECT  *
FROM    @A AS A
        LEFT JOIN @B AS B ON A.ID = B.ID
                             AND B.Value = 3;

--Returns 1 row.
SELECT  *
FROM    @A AS A
        LEFT JOIN @B AS B ON A.ID = B.ID
WHERE   B.Value = 3;

Open in new window

Scott PletcherSenior DBACommented:
Hmm, there is a difference when using OUTER JOINS

True, I should have stated that explicitly.  But I did clearly state in my original comments:
For INNER/equi joins...
which that code sample was.
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Although my example addressed INNER JOIN and TableA.Field1 = 3

the discussion has migrated to OUTER JOIN and TableB.Field1 = 3

So from the latest couple of posts, the which includes the conditional test TableB.Field1 = 3 in the JOIN clause:
SELECT  A.Field1 ,
        A.Field2 ,
        B.Field3
FROM    TableA AS A
        LEFT JOIN TableB AS B ON TableA.PKField = TableB.FKField
                                  AND TableB.Field1 = 3;

Open in new window


is equivalent (resulting recordset) to:
SELECT  A.Field1 ,
        A.Field2 ,
        B.Field3
FROM    TableA AS A
LEFT JOIN (SELECT * FROM TableB WHERE Field1 = 3) AS B 
ON TableA.PKField = TableB.FKField

Open in new window

I assume that the execution plan for these would be similar?
ste5anSenior DeveloperCommented:
Without testing, I guess so.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.