Restricting a join

To make a cross product join useful, you need to include only rows that satisfy some condition in the result. That condition, called the join condition, compares one column from one table to one column in the other table, using a comparison operator (=, =>, <, etc.). You thus eliminate some of the rows from the cross product result.

For example, to make the join in the preceding section useful, you could insist that the sales_rep in the sales_order table be 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.

To do this, add a WHERE clause to the previous query to show the list of employees and their course registrations:

SELECT * 
FROM sales_order, employee 
WHERE sales_order.sales_rep = employee.emp_id 

The table name is given as a prefix to identify the columns. Although not strictly required in this case, using the table name prefix clarifies the statement, and is required when two tables have a column with the same name. A table name used in this context is called a qualifier.

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

The following query is a modified version that fetches only some of the columns and orders the results.

SELECT employee.emp_lname, sales_order.id,
sales_order.order_date
FROM sales_order, employee 
WHERE sales_order.sales_rep = employee.emp_id 
ORDER BY employee.emp_lname

If there are many tables in a SELECT command, you may need to type several qualifier names. You can reduce typing by using a correlation name.

A correlation name is an alias for a particular instance of a table. This alias is valid only within a single statement. Correlation names are created by putting a short form for a table name immediately after the table name, separated by the keyword AS. You then must use the short form as a qualifier instead of the corresponding table name.

SELECT E.emp_lname, S.id, S.order_date 
FROM sales_order AS S, employee AS E 
WHERE S.sales_rep = E.emp_id
ORDER BY E.emp_lname

Here, two correlation names S and E are created for the sales_order and employee tables.