Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 86

Note that the SQL ON clause does not replace the SQL WHERE clause, which can still be used
to determine which rows will be displayed. For example, we can use the SQL WHERE clause to
limit the records shown to those for the OrderYear of 2012:
/* *** SQL-Query-CH02-59 *** */
SELECT *
FROM RETAIL_ORDER JOIN ORDER_ITEM
ON RETAIL_ORDER.OrderNumber=ORDER_ITEM.OrderNumber
WHERE OrderYear = '2012'
ORDER BY RETAIL_ORDER.OrderNumber, ORDER_ITEM.SKU;
The result is:
You can use the SQL JOIN ON syntax as an alternate format for joins of three or more
tables as well. If, for example, you want to obtain a list of the order data, order line data and
SKU data, you can use the following SQL statement:
/* *** SQL-Query-CH02-60 *** */
SELECT RETAIL_ORDER.OrderNumber, StoreNumber, OrderYear,
ORDER_ITEM.SKU, SKU_Description, Department
FROM RETAIL_ORDER JOIN ORDER_ITEM
ON RETAIL_ORDER.OrderNumber=ORDER_ITEM.OrderNumber
JOIN SKU_DATA
ON ORDER_ITEM.SKU=SKU_DATA.SKU
WHERE OrderYear = '2012'
ORDER BY RETAIL_ORDER.OrderNumber, ORDER_ITEM.SKU;
The result is:
You can make that statement even simpler by using the SQL AS keyword to create table
aliases as well as for naming output columns:
/* *** SQL-Query-CH02-61 *** */
SELECT RO.OrderNumber, StoreNumber, OrderYear,
OI.SKU, SKU_Description, Department
FROM RETAIL_ORDER AS RO JOIN ORDER_ITEM AS OI
ON RO.OrderNumber=OI.OrderNumber
JOIN SKU_DATA AS SD
ON OI.SKU=SD.SKU
WHERE OrderYear = '2012'
ORDER BY RO.OrderNumber, OI.SKU;