Restricting Joins

Adding conditions to a query restricts the result set to records that meet specific criteria. Join conditions use comparison operators to eliminate rows from the cross-product result.

Example 1

The WHERE clause in this query specifies that the SalesRepresentative in the SalesOrders table match the EmployeeID in the Employees table. This creates a result set where each row contains information about an order and the sales representative responsible for it:

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.

Example 2

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.

Correlation Names

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.

Note:

A table name or correlation name is needed only to resolve ambiguity if two columns of different tables have the same name. If you have created a correlation name, you must use it instead of the full table name; however, if you have not created a correlation name, use the full table name.