JOIN

relation [ LEFT ] JOIN relation ON join_condition

relation, relation [, …]> WHERE join_condition [, AND join_condition]

[ 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.

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

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