ANSI Inner and Outer Joins

This is the ANSI syntax for joining tables:

left_table [inner | left [outer] | right [outer]] join right_table 
on left_column_name  = right_column_name

The result of the join between the left and the right tables is called a joined table. Joined tables are defined in the from clause. For example:

select titles.title_id, title, ord_num, qty
from titles left join salesdetail
on titles.title_id = salesdetail.title_id

title_id title              ord_num                qty
----------------------------- -------------------- -----
BU1032  The Busy Executive   AX-532-FED-452-2Z7   200
BU1032  The Busy Executive   NF-123-ADS-642-9G3   1000
. . .
TC7777  Sushi, Anyone?       ZD-123-DFG-752-9G8   1500
TC7777  Sushi, Anyone?       XS-135-DER-432-8J2   1090
(118 rows affected)

ANSI join syntax allows you to write either:

• Inner joins, in which the joined table includes only the rows of the inner and outer tables that meet the conditions of the on clause. The result set of a query that includes an inner join does not include any null-supplied rows for the rows of the outer table that do not meet the conditions of the on clause.

• Outer joins, in which the joined table includes all the rows from the outer table, whether or not they meet the conditions of the on clause. If a row does not meet the conditions of the on clause, values from the inner table are stored in the joined table as null values. The where clause of an ANSI outer join restricts the rows that are included in the query result.