sql server - ON is part of syntax

Question

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?

Answer 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