Restricting a join

To make a cross product join useful, you normally want 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, such as =, =>, <, and so on. Thus, you 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 SalesRepresentative in the SalesOrders table be the same as the one in the Employees 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 SalesOrders, Employees 
WHERE SalesOrders.SalesRepresentative = Employees.EmployeeID 

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 following query is a modified version that fetches only some of the columns and orders the results:

SELECT Employees.Surname, SalesOrders.id,
SalesOrders.OrderDate
FROM SalesOrders, Employees 
WHERE SalesOrders.SalesRepresentative = Employees.EmployeeID 
ORDER BY Employees.Surname

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. Then you must use the short form as a qualifier instead of the corresponding table name:

SELECT E.Surname, S.id, S.OrderDate 
FROM SalesOrders AS S, Employees AS E 
WHERE S.SalesRepresentative = E.EmployeeID
ORDER BY E.Surname

Here, two correlation names S and E are created for the SalesOrders and Employees tables.