Proper way to write a SQL syntax?

Question

These two SQL syntaxtes produces the same result, which one is better to use and why?

1st:

SELECT c.Id,c.Name,s.Id,s.Name,s.ClassId 
FROM dbo.ClassSet c,dbo.StudentSet s WHERE c.Id=s.ClassId

2nd:

SELECT c.Id,c.Name,s.Id,s.Name,s.ClassId 
FROM dbo.ClassSet c JOIN dbo.StudentSet s ON c.Id=s.ClassId

That *comma-separated list of tables* style was discontinued with the **ANSI-92** SQL Standard (more than **20 years** ago) - also see [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) for more information on this — marc_s, Nov 23 '13 at 12:44

score 3 · Accepted Answer · edited May 23 '17 at 12:28

3

The 2:nd one is better.

The way youre joining in the first query in considered outdated. Avoid using , and use JOIN

"In terms of precedence, a JOIN's ON clause happens before the WHERE clause. This allows things like a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL to check for cases where there is NOT a matching row in b."

"Using , notation is similar to processing the WHERE and ON conditions at the same time"

Found the details about it here, MySQL - SELECT, JOIN

Read more about SQL standards

http://en.wikipedia.org/wiki/SQL-92

edited May 23 '17 at 12:28

Community

1
1

answered Nov 23 '13 at 11:31

Mad Dog Tannen

7,129
5
31
55

This is the closest correct answer. The prior style of joining is called "SQL-89" style and was the only method available for over a decade leading up to the new style introduced in SQL-92. Since then the new style has been the preferred style of both the standard, and for it's readability. The old style is still supported in most SQL products but is also deprecated and planned to be removed in the future. SQL-89 style joins should not be learned/used by new SQL students, nor should professors/teachers teach it to them, as employers are less likely to hire those who use it. – RBarryYoung Nov 23 '13 at 12:44
@RBarryYoung I have never heard that the old style is deprecated (or plans to be.) Can you provide a reference? – ypercubeᵀᴹ Nov 23 '13 at 12:58
@ypercube: Here's one. I'll see if I can find more official statements. I know for a fact that the old style outer join equality operators (`*=`, etc.) have been deprecated or phased out by some of the vendor products. – RBarryYoung Nov 23 '13 at 13:16
@RBarryYoung `*=` was never in the standard. It was only implemented in SQL-Server (and maybe Sybase?) – ypercubeᵀᴹ Nov 23 '13 at 13:23
@ypercube: Right, I forgot that Oracle used the `(+) =` syntax, which wasn't standard either. – RBarryYoung Nov 23 '13 at 13:40

Suganthan Madhavan Pillai · Answer 2 · 2013-11-23T11:36:26.410

1

SELECT c.Id,c.Name,s.Id,s.Name,s.ClassId FROM dbo.ClassSet c JOIN dbo.StudentSet s ON c.Id=s.ClassId

Without any doubt the above one is better when comparing to your first one.In the precedence table "On" is sitting Second and "Where" is on fourth

But for the simpler query like you don't want to break your head like this, for project level "JOIN" is always recommended

edited Nov 23 '13 at 11:36

answered Nov 23 '13 at 11:31

Suganthan Madhavan Pillai

5,495
9
51
84

score 0 · Answer 3 · edited May 23 '17 at 12:28

Check this link Is a JOIN faster than a WHERE?

Answer by @MehrdadAfshari

Theoretically, no, it shouldn't be any faster. The query optimizer should be able to generate an identical execution plan. However, some DB engines can produce better execution plans for one of them (not likely to happen for such a simple query but for complex enough ones). You should test both and see (on your DB engine).

score -1 · Answer 4 · answered Nov 23 '13 at 12:30

-1

The second because it's more readable. That is all.

answered Nov 23 '13 at 12:30

LoztInSpace

5,584
1
15
27

Proper way to write a SQL syntax?

4 Answers4