CODEWITHSUNDEEP
sql
PBJ
PBJ

Reputation: 384

SQL query, joins without "on" clause and some with "on" clause

I need some confirmation that this query is doing what I think it is. Today I learned of this "old school" method of using a comma to join tables - and they do not have an on-clause specified. Yet, some other tables are also joined and do have on-clauses. Here is the query: 

SELECT a.Col1, a.Col2, b.Col3
FROM b, a
JOIN c ON c.Name = a.Name
AND c.ID = a.ID

My understanding from my research so far, is that the comma represents a cross join, hence there needs not a column equality specifier ("on" clause) because a cross join simply joins each row from the first table to each row from the second table.

...so then, when applying the joins for table c, do those get applied before or after the cross join, or does it matter?

What would be a re-written example of this that uses the "Inner Join" "Cross Join" words instead of the commas?

Upvotes: 1

Views: 485

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

The correct way to write the query is:

SELECT a.Col1, a.Col2, b.Col3
FROM b CROSS JOIN
     a INNER JOIN c
     ON c.Name = a.Name AND c.ID = a.ID;

There is no need to learn about commas. They are obsolete and should not be used.

Upvotes: 1

Related Questions