JOIN
Explicit syntax (ANSI SQL-92)
relation [ LEFT ] JOIN relation ON join_condition
Implicit syntax (ANSI SQL-89)
relation, relation [, …]> WHERE join_condition [, AND join_condition]
Join type
[ INNER ] JOIN LEFT [ OUTER ] JOIN RIGHT [ OUTER ] JOIN FULL [ OUTER ] JOIN CROSS JOIN
ON join_condition
join_condition is an expression resulting in a value of type boolean (similar to a WHERE clause) that specifies which rows in a join are considered to match.
For implicit syntax (ANSI SQL-89) then *= and =* operators in the join_condition are used to denote “left outer join” and “right outer join” respectively.
Example - explicit syntax
select l_orderkey, o_orderdate, o_shippriority, COUNT(*) from customer JOIN orders ON c_custkey = o_custkey JOIN lineitem ON l_orderkey = o_orderkey group by l_orderkey, o_orderdate, o_shippriority
Example - implicit syntax
select l_orderkey, o_orderdate, o_shippriority, COUNT(*) from customer, orders, lineitem where and c_custkey = o_custkey and l_orderkey = o_orderkey group by l_orderkey, o_orderdate, o_shippriority