How to Use a Basic Join in SQL

By Allen G. Taylor

Any multitable SQL query is a type of join. The source tables are joined in the sense that the result table includes information taken from all the source tables. The simplest join is a two-table SELECT that has no WHERE clause qualifiers: Every row of the first table is joined to every row of the second table.

The result table is the Cartesian product of the two source tables. The number of rows in the result table is equal to the number of rows in the first source table multiplied by the number of rows in the second source table.

For example, imagine that you’re the personnel manager for a company and that part of your job is to maintain employee records. Most employee data, such as home address and telephone number, is not particularly sensitive. But some data, such as current salary, should be available only to authorized personnel. To maintain security of the sensitive information, keep it in a separate table that is password protected.

Consider the following pair of tables:

EMPLOYEE       COMPENSATION
--------       ------------
EmpID       Employ
FName       Salary
LName       Bonus
City
Phone

Fill the tables with some sample data:

EmpID FName  LName  City  Phone
----- -----  -----  ----  -----
 1 Whitey Ford  Orange  555-1001
 2 Don  Larson  Newark  555-3221
 3 Sal  Maglie  Nutley  555-6905
 4 Bob  Turley  Passaic  555-8908
Employ Salary Bonus
------ ------ -----
  1 33000 10000
  2 18000  2000
  3 24000  5000
  4 22000  7000

Create a virtual result table with the following query:

SELECT *
 FROM EMPLOYEE, COMPENSATION ;

Here’s what the query produces:

EmpID FName LName City Phone Employ Salary Bonus
----- ----- ----- ---- ----- ------ ------ -----
 1 Whitey Ford Orange 555-1001  1 33000 10000
 1 Whitey Ford Orange 555-1001  2 18000 2000
 1 Whitey Ford Orange 555-1001  3 24000 5000
 1 Whitey Ford Orange 555-1001  4 22000 7000
 2 Don Larson Newark 555-3221  1 33000 10000
 2 Don Larson Newark 555-3221  2 18000 2000
 2 Don Larson Newark 555-3221  3 24000 5000
 2 Don Larson Newark 555-3221  4 22000 7000
 3 Sal Maglie Nutley 555-6905  1 33000 10000
 3 Sal Maglie Nutley 555-6905  2 18000 2000
 3 Sal Maglie Nutley 555-6905  3 24000 5000
 3 Sal Maglie Nutley 555-6905  4 22000 7000
 4 Bob Turley Passaic 555-8908  1 33000 10000
 4 Bob Turley Passaic 555-8908  2 18000 2000
 4 Bob Turley Passaic 555-8908  3 24000 5000
 4 Bob Turley Passaic 555-8908  4 22000 7000

The result table, which is the Cartesian product of the EMPLOYEE and COMPENSATION tables, contains considerable redundancy. Furthermore, it doesn’t make much sense. It combines every row of EMPLOYEE with every row of COMPENSATION.

The only rows that convey meaningful information are those in which the EmpID number that came from EMPLOYEE matches the Employ number that came from COMPENSATION. In those rows, an employee’s name and address are associated with his or her compensation.

When you’re trying to get useful information out of a multitable database, the Cartesian product produced by a basic join is almost never what you want, but it’s almost always the first step toward what you want. By applying constraints to the JOIN with a WHERE clause, you can filter out the unwanted rows.