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

Comparing Subqueries and Joins
Subqueries and joins both process multiple tables, but they differ slightly. As mentioned
earlier, a subquery can only be used to retrieve data from the top table. A join can be used to
obtain data from any number of tables. Thus, a join can do everything a subquery can do, and
more. So why learn subqueries? For one, if you just need data from a single table, you might
use a subquery because it is easier to write and understand. This is especially true when pro-
cessing multiple tables.
In Chapter 8, however, you will learn about a type of subquery called a correlated sub-
query . A correlated subquery can do work that is not possible with joins. Thus, it is important
for you to learn about both joins and subqueries, even though right now it appears that joins
are uniformly superior. If you're curious, ambitious, and courageous, jump ahead and read the
discussion of correlated subqueries on pages 368-370.

The SQL JOIN ON Syntax
So far, we have learned to code SQL joins using the following syntax:

/* *** SQL-Query-CH02-53 *** */
SELECT *
FROM RETAIL_ORDER, ORDER_ITEM
WHERE RETAIL_ORDER.OrderNumber=ORDER_ITEM.OrderNumber
ORDER BY RETAIL_ORDER.OrderNumber, ORDER_ITEM.SKU;

However, there is another way to code this join. In this second case, we use the SQL JOIN ON
syntax :

/* *** SQL-Query-CH02-58 *** */
SELECT *
FROM RETAIL_ORDER JOIN ORDER_ITEM
ON RETAIL_ORDER.OrderNumber=ORDER_ITEM.OrderNumber
ORDER BY RETAIL_ORDER.OrderNumber, ORDER_ITEM.SKU;

The result is:

These two join syntaxes are equivalent, and it is a matter of personal preference which one
you use. Some people think that the SQL JOIN ON syntax is easier to understand than the first.
Note that when using the SQL JOIN ON syntax:

●

The SQL JOIN keyword is placed between the table names in the SQL FROM
clause, where it replaces the comma that previously separated the two table
names, and

●

The SQL ON keyword now leads into an SQL ON clause, which includes the state-
ment of matching key values that was previously in an SQL WHERE clause.