ON is part of the syntax

Question

Can I write an inner join or outer join without defining a condition? Is ON condition part of the join condition syntax?

Answer 1

when using ANSI SQL-92 syntax, the ON keyword is part of the join, except for cross join , since you have no 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 follow the joined table (joined as INNER and OUTER ), so you won't have a syntax error. but if you use the ANSI SQL-89 syntax, the ON keyword is omitted, but you must specify the relation on where clause

ex.

INNER JOIN

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

CROSS JOIN

 SELECT * FROM tableA, tableB 

it is error prone because if you forget this condition it will not generate a syntax error and most likely will make a cross join

Answer 2

No, you do not need to specify ON , you can do it in WHERE :

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

but it is error prone (easily ends with CROSS JOIN s) and is generally disapproving. See here for arguments against him.

EDIT . To be more precise, you can JOIN use the old-style syntax shown above, which does not require ON , but if you explicitly subscribe to using INNER | LEFT | FULL INNER | LEFT | FULL INNER | LEFT | FULL etc. then yes, ON is part of the syntax. MSDN with exact syntax requirements.

Answer 3

You can perform an implicit join without using join syntax:

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

But I am sure that this is necessary for explicit associations. You will receive an invalid syntax error. Explicit join value:

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