Selecting Data from Multiple Tables

Suppose that you want to select the FIRST_NAME, LAST_NAME, and DEPARTMENT_NAME of every employee. FIRST_NAME and LAST_NAME are in the EMPLOYEES table, and DEPARTMENT_NAME is in the DEPARTMENTS table. Both tables have DEPARTMENT_ID. You can use the query in the following example. Such a query is called a join.

Selecting Data from Two Tables (Joining Two Tables)

SELECT EMPLOYEES.FIRST_NAME "First",
EMPLOYEES.LAST_NAME "Last",
DEPARTMENTS.DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

Result:

First                Last                      Dept. Name
-------------------- ------------------------- ------------------------------
Jennifer             Whalen                    Administration
Michael              Hartstein                 Marketing
Pat                  Fay                       Marketing
Den                  Raphaely                  Purchasing
Karen                Colmenares                Purchasing
Alexander            Khoo                      Purchasing
Shelli               Baida                     Purchasing
Sigal                Tobias                    Purchasing
Guy                  Himuro                    Purchasing
Susan                Mavris                    Human Resources
Donald               OConnell                  Shipping
 
First                Last                      Dept. Name
-------------------- ------------------------- ------------------------------
Douglas              Grant                     Shipping
...
Shelley              Higgins                   Accounting
 
106 rows selected.

Table-name qualifiers are optional for column names that appear in only one table of a join, but are required for column names that appear in both tables. The following query is equivalent to the query in the preceding example:

SELECT FIRST_NAME "First",
LAST_NAME "Last",
DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

To make queries that use qualified column names more readable, use aliases, as in the following example:

SELECT FIRST_NAME "First",
LAST_NAME "Last",
DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;

Although you create the aliases in the FROM clause, you can use them earlier in the query, as in the following example:

SELECT e.FIRST_NAME "First",
e.LAST_NAME "Last",
d.DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;

Selecting Data from Multiple Tables

Related Topics