Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I write a query like this...

select *
from table1 t1
join table2 t2
on t1.id = t2.id

Does the SQL optimizer, not sure if that is the correct term, translate that to...

select *
from table1 t1, table2 t2
where t1.id = t2.id

Essentially, is the Join statement in SQL Server just an easier way to write sql? Or is it actually used at run-time?

Edit: I almost always, and will almost always, use the Join syntax. I am just curious what happens.

share|improve this question
1  
Could you elaborate on the "almost"? When would you use the old-style syntax and why? –  Aaron Bertrand Feb 27 '13 at 17:08
2  
One edge case where it does make a difference is if you add a (deprecated) GROUP BY ALL in –  Martin Smith Feb 27 '13 at 17:20
    
@MartinSmith does anybody use GROUP BY ALL on purpose? :-) –  Aaron Bertrand Feb 27 '13 at 17:24
    
@AaronBertrand - I doubt it! Don't think I've ever seen anyone use it. –  Martin Smith Feb 27 '13 at 17:27
4  
The query optimizer does not write, or rewrite SQL. Users specify desired results using the language SQL. The parser turns SQL into a logical relational tree. The optimizer explores logical alternatives for that relational tree, finds physical execution implementations, and chooses a good one as soon as it can. The result is translated to an internal program for the execution engine. Nothing past the parser uses SQL (except distributed query, but that involves translating the internal tree back to SQL). –  Paul White Feb 28 '13 at 2:09
show 1 more comment

migrated from stackoverflow.com Feb 27 '13 at 17:21

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 15 down vote accepted

Those collapse to the same thing internally. The former is the one you should always write. More importantly, why does it matter? They're identical in terms of execution plan and performance (assuming you don't mess it up, which is easier to do with the lazy, old-style syntax).

Here is proof using AdventureWorks that there is no CROSS JOIN and filter going on.


The explicit join:

enter image description here


The implicit join:

enter image description here


Look, ma! Identical plans, identical results, no cross joins or filters seen anywhere.

(For clarity, the warning on the SELECT operator in both cases is a cardinality-affecting implicit convert, nothing to do with the join in either case.)

share|improve this answer
    
I almost always, and will almost always, use the Join syntax. I am just curious what happens. –  Mr. Ant Feb 27 '13 at 16:42
3  
+1 for teaching me something that I didn't know. –  Massimiliano Peluso Feb 27 '13 at 16:51
    
TIL you can give two up votes for the same question if it has been migrated. –  Kermit Feb 27 '13 at 17:59
    
@sigh yep, the key, I suspect, involves UserID, which is different per site. –  Aaron Bertrand Feb 27 '13 at 18:02
add comment

Strictly speaking, there is a difference in the input to the query optimizer between the two forms:

-- Input tree (ISO-89)
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM 
    Production.Product AS p,
    Production.ProductInventory AS inv
WHERE
    inv.ProductID = p.ProductID
GROUP BY
    p.Name
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 3604);

ISO-89 input tree

-- Input tree (ISO-92)
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM Production.Product AS p
JOIN Production.ProductInventory AS inv ON
    inv.ProductID = p.ProductID
GROUP BY
    p.Name
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 3604);

ISO-92 input tree

As you can see, the ON clause predicate is tightly bound to the join using the modern syntax. With the older syntax, there is a logical cross join followed by a relational select (a row filter).

The query optimizer almost always collapses the relational select into the join during optimization, meaning the two forms will very likely produce equivalent query plans, but there is no actual guarantee.

share|improve this answer
add comment

OK, I was curious so I did a test. I got actual execution plans for the following.

select * 
from sys.database_principals prin, sys.database_permissions perm
WHERE prin.principal_id = perm.grantee_principal_id

and

select * 
from sys.database_principals prin
JOIN sys.database_permissions perm
    ON prin.principal_id = perm.grantee_principal_id

I compared them object by object and they were identical. So at least for a very simple example, they came out to the same thing. I also checked statistics IO and time and they were close enough to be the same thing.

That being said, you should use the JOIN syntax because it's easier to read and you are less likely to make mistakes, particularly in complicated queries. And the *= / =* syntax for OUTER joins has already been removed as of SQL-Server 2005.

share|improve this answer
4  
The ANSI 89 syntax for inner join is not being deprecated. –  Martin Smith Feb 27 '13 at 17:16
2  
@KennethFisher You are right that *= / =* is depreciated (it seems you fell victim of SE format and the stars were removed). The ANSI-89 syntax for joins is not deprecated and due to backwards compatibility reasons, I don't think there is plan to. –  ypercube Feb 27 '13 at 17:25
1  
Agreed, the old style join (from a, b) is not deprecated, and is unlikely to ever be deprecated. But just because it isn't being deprecated doesn't mean anybody should be using it. –  Aaron Bertrand Feb 27 '13 at 17:28
    
For some reason I could have sworn I had read that it was. However I just checked 2012 BOL and it does say very clearly that the old style is supported. How about this then .. You should use the JOIN syntax because it's easier to read and you are less likely to make mistakes, particularly in complicated queries. –  Kenneth Fisher Feb 27 '13 at 17:43
add comment

For inner join they are interchangeable, but for Outer Joins they have different meanings - the ON is matching and WHERE is simple filtering. So its better to stick to the correct JOIN syntax matching on the ON.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.