ON is part of syntax

问题:

Is it possible to write inner join or outer join without specfying the condition? Is ON condition is part of the syntax of join condition?

回答1:

when using the ANSI SQL-92 syntax, the ON keyword is part of the join except for the cross join since you don't have a condition for the relationship.

ex.

INNER JOIN

SELECT *
FROM tableA INNER JOIN tableB
       on tableA.ID = tableB.ID

CROSS JOIN

SELECT *
FROM tableA CROSS JOIN tableB

ON should be followed after the joined table (joins like INNER and OUTER) so you won't have syntax error. but if you're using the ANSI SQL-89 syntax, ON keyword is omitted but you have to specify the relationship on the where clause

ex.

INNER JOIN

SELECT *
FROM tableA, tableB
WHERE tableA.ID = tableB.ID

CROSS JOIN

SELECT *
FROM tableA, tableB

it is prone to error because if you forget the condition, it won't generate syntax error and will likely do the cross join

回答2:

No, you don't have to specify the ON, you could do so in a WHERE:

SELECT *
FROM tableA, tableB
WHERE tableA.Id = tableB.Id

but this is prone to error (easy to end up with CROSS JOINs) and is generally frowned upon. See here for arguments against it.

EDIT: To be more precise, you can JOIN using the old style syntax shown above, which doesn't require an ON, but if you explicitly join using INNER | LEFT | FULL etc. then yes, ON is part of the syntax. MSDN page with the precise syntax requirements.

回答3:

You can do an implicit join without using the join-on syntax:

select a.fred, b.joe from tableApple as a, tableBread as b
where a.key1 = b.key1

But I'm pretty certain on is required for explicit joins. You will get an incorrect syntax error. Explicit join meaning:

select a.fred, b.joe 
from tableApple as a
left join tableBread as b
on a.key1 = b.key1
where a.key1 = 'sally'