Joining tables

You can use a single SELECT statement to retrieve data from more than one table in the database. The statement joins the tables and returns a single recordset containing selected data from each table.

For example, a company database might contain one table with personal data about employees and another table with data about the company’s departments. If you want to build an employee directory that displays an employee’s name, phone number, and department, you must retrieve information from the two tables simultaneously.

To do this, create a join specifying all the tables to include and how the tables are related to each other. Here’s an example:

SELECT FIRSTNAME, LASTNAME, PHONE, DEPTNAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPT_ID = DEPARTMENTS.DEPT_ID

   

NOTE

 

Use dot notation to identify the columns more precisely. For example, EMPLOYEES.DEPT_ID refers to the DEPT_ID column in the EMPLOYEES table.

The first line specifies the columns to retrieve. The first three columns--FIRSTNAME, LASTNAME, PHONE--exist in the EMPLOYEES table, while the fourth column--DEPTNAME--exists only in the DEPARTMENTS table.

The second line specifies the two tables from which to retrieve data, EMPLOYEES and DEPARTMENTS.

The final line specifies the records to join and retrieve from the two tables. Each table has a column called DEPT_ID. (In the DEPARTMENTS table, the column is the primary key. For more information, see Defining relationships between the tables.) The WHERE clause compares the value of DEPT_ID in one table to the value of DEPT_ID in the other table. When a match is found, all the fields of the record in the EMPLOYEES table are joined with all the fields of the record in the DEPARTMENTS table. Next, the combined data is filtered to create a new record made up of a FIRSTNAME, LASTNAME, PHONE, and DEPTNAME column. Finally, the new record is added to the recordset.

Using slightly different join syntax may be preferable in some database systems. For example, the following SQL statement uses the SQL keywords INNER JOIN...ON to achieve the same results as the previous example:

SELECT FIRSTNAME, LASTNAME, PHONE, DEPTNAME
FROM EMPLOYEES INNER JOIN DEPARTMENTS
ON EMPLOYEES.DEPT_ID = DEPARTMENTS.DEPT_ID

Consult your database system’s documentation to determine which join syntax you should use.