ASA Getting Started
  Selecting Data from Multiple Tables

Using the ON phrase to restrict a join

The ON phrase applies a restriction to the rows in a join, in much the same way that the WHERE clause applies restrictions to the rows of a query.

The ON phrase allows more useful joins than the CROSS JOIN to be constructed. For example, you can apply the ON phrase to a join of the sales_order and employee table to retrieve only those rows for which the sales_rep in the sales_order table is the same as the one in the employee table in every row of the result. Then each row contains information about an order and the sales representative responsible for it.

List all sales orders with their dates, and the employee responsible for each

• In Interactive SQL, type the following in the SQL Statements pane and press F5 to execute the statement.

  SELECT employee.emp_lname,
         sales_order.id,
         sales_order.order_date
  FROM sales_order JOIN employee
     ON sales_order.sales_rep = employee.emp_id

  emp_lname	id	order_date
  Chin	2008	4/2/2001
  Chin	2020	3/4/2001
  Chin	2032	7/5/2001
  Chin	2044	7/15/2000
  Chin	2056	4/15/2001
  ...	...	...

Notes 

The table name is given as a prefix to identify the columns. Using the table name prefix clarifies the statement, and is required when two tables have a column with the same name.

The results of this query contain only 648 rows (one for each row in the sales_order table). Of the 48,600 rows in the cross product, only 648 of them have the employee number equal in the two tables.

The ordering of the results has no meaning. You could add an ORDER BY clause to impose a particular order on the query.

The ON clause includes columns that are not included in the final result set.

For more information, see Explicit join conditions (the ON phrase).