SQL Server Query Question

Question

This might be a very simple question, but am just curious about it.

Are these queries both representations of equi join?

I find that both returns the same result.

Query1:

SELECT C.* FROM [Case] A, [Event] B, Notice C
WHERE A.CaseID = B.CaseID
AND B.EventID = C.EventID

Query2:

SELECT C.* FROM [Case] A
join [Event] B on A.CaseID = B.CaseID
join Notice C on B.EventID = C.EventID

Please clarify.

Answer 1

Yes, same queries, different syntax.

The second query is better written as:

SELECT C.* 
FROM [Case] A 
inner join [Event] B on A.CaseID = B.CaseID 
inner join Notice C on B.EventID = C.EventID

The second query using ANSI syntax has some advantages over the first:

• easy to see when you have a missing ON clause
• makes it clear what kind of join is being done
• separates JOIN clause from WHERE clause

Answer 2

When using the comma in the FROM clause, it's the same as the keyword JOIN. Usually, you'd be using INNER JOIN or a form of OUTER JOIN which gives much more efficient results.