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 companys departments. If you want to build an employee directory that displays an employees 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. Heres 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 systems documentation to determine which join syntax you should use.