Displaying Data From Multiple Tables

You can use SELECT to display data from multiple tables. This process is referred to as joining tables. In a join, rows from multiple tables are usually linked by similar columns.

Joining tables is useful when you need to view data that is stored in multiple tables. For example, the employees table contains employee information with a column of department IDs, but not the department names. The departments table contains columns for department IDs and names. By joining the tables on the department ID, you can view an employee's information with the corresponding department name.

There are several types of joins, including self, inner, and outer. A self-join joins a table to itself. Example: Self Joining a Table With the SQL JOIN ON Syntax is an example of a self- join. An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition. Any unmatched rows are not displayed in the output. Example: Selecting Data From Two Tables With the SQL NATURAL JOIN Syntax and Example: Selecting Data From Multiple Tables WIth the SQL JOIN USING Syntax are examples of inner joins. An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition. There are three types of outer joins: LEFT OUTER, RIGHT OUTER, and FULL OUTER. Example: Using SQL Outer Joins shows examples of a outer joins.

When you retrieve data from multiple tables, you can explicitly identify to which table a column belongs. This is important when tables contain columns with the same name. You can use the complete table name to explicitly identify a column, such as employees.employee_id, or a table alias. Note the use of the table aliases (d, e, and l) to explicitly identify the columns by table in the SQL statement in Example: Selecting Data From Multiple Tables WIth the SQL JOIN USING Syntax and Example: Selecting Data From Multiple Tables With the SQL JOIN ON Syntax. The alias is defined in the FROM clause of the SQL statement. A table alias is used, rather than the table name, to simplify and reduce the size of the SQL code.

You can join two tables automatically on all the columns that have matching names and datatypes using the NATURAL JOIN syntax as shown in Example: Selecting Data From Two Tables With the SQL NATURAL JOIN Syntax. This join select rows from the two tables that have equal values in the matched columns. If the columns with the same name have different datatypes, an error results.

Selecting Data From Two Tables With the SQL NATURAL JOIN Syntax

-- the following SELECT statement retrieves data from two tables
-- that have a corresponding column(s) with equal values
-- for employees and departments, matching columns are department_id, manager_id
SELECT employee_id, last_name, first_name, department_id, 
  department_name, manager_id  FROM employees
  NATURAL JOIN departments;

Example: Selecting Data From Multiple Tables WIth the SQL JOIN USING Syntax is an example of querying data from joined tables using the JOIN ... USING syntax. The first SELECT joins two tables, and the second SELECT joins three tables. With the JOIN ... USING syntax, you explicitly specify the join columns. The columns in the tables that are used for the join must have the same name. Note that the table alias is not used on the referenced columns.

Selecting Data From Multiple Tables WIth the SQL JOIN USING Syntax

-- the following SELECT statement retrieves data from two tables
-- that have a corresponding column (department_id)
-- note that the employees table has been aliased to e and departments to d
SELECT e.employee_id, e.last_name, e.first_name, e.manager_id, department_id, 
  d.department_name, d.manager_id FROM employees e 
  JOIN departments d USING (department_id);

-- the following SELECT retrieves data from three tables
-- two tables have the corresponding column (department_id) and 
-- two tables have the corresponding column (location_id)
SELECT e.employee_id, e.last_name, e.first_name, e.manager_id, department_id,
  d.department_name, d.manager_id, location_id, l.country_id FROM employees e
  JOIN departments d USING (department_id)
  JOIN locations l USING (location_id);

Example: Selecting Data From Multiple Tables With the SQL JOIN ON Syntax is an example of querying data from joined tables using JOIN ... ON syntax. The first SELECT joins two tables, and the second SELECT joins three tables. Using the ON clause enables you to specify a join condition outside a WHERE clause and a join condition with columns that have different name, but equal values.

Selecting Data From Multiple Tables With the SQL JOIN ON Syntax

-- the following SELECT statement retrieves data from two tables
-- that have a corresponding column department_id
-- note that the employees table has been aliased to e and departments to d
SELECT e.employee_id, e.last_name, e.first_name, e.department_id, 
  d.department_name, d.manager_id FROM employees e 
  JOIN departments d ON e.department_id = d.department_id
  WHERE e.manager_id = 122;

-- the following SELECT retrieves data from three tables
-- two tables have the corresponding column department_id and 
-- two tables have the corresponding column location_id
SELECT e.employee_id, e.last_name, e.first_name, e.department_id,
  d.department_name, d.manager_id, d.location_id, l.country_id FROM employees e
  JOIN departments d ON e.department_id = d.department_id
  JOIN locations l ON d.location_id = l.location_id
  WHERE l.location_id = 1700;

You can join a table to itself, a process called a self-join. For example, if you want to view an employee ID and employee last name with the manager ID and manager name of that employee, you would use a self-join on the employees table as shown in Example: Self Joining a Table With the SQL JOIN ON Syntax. The employees table is joined to itself using the manager ID of the employee and employee ID of the manager. Note that the columns used for the join have different names. Column aliases, such as emp_id and emp_lastname, were used to clearly identify the column values in the output.

Self Joining a Table With the SQL JOIN ON Syntax

-- the following SELECT statement retrieves data from the employees table
-- to display employee_id and last_name, along with manager_id and last_name
-- of the employee in a self-join
-- note that the employees table has been aliased to e and m
SELECT e.employee_id emp_id, e.last_name emp_lastname, m.employee_id mgr_id,
  m.last_name mgr_lastname
  FROM employees e 
  JOIN employees m ON e.manager_id = m.employee_id;

Example: Using SQL Outer Joins shows how to use outer joins.

Using SQL Outer Joins

-- the following uses a LEFT OUTER JOIN
-- all rows are retrieved from the left table (employees) even if 
-- there is no match in the right table (departments)
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  FROM employees e LEFT OUTER JOIN departments d
  ON (e.department_id = d.department_id);

-- the following uses a RIGHT OUTER JOIN
-- all rows are retrieved from the right table (departments) even if 
-- there is no match in the left table (employees)
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  FROM employees e RIGHT OUTER JOIN departments d 
  ON (e.department_id = d.department_id);

-- the following uses a FULL OUTER JOIN
-- all rows are retrieved from the employees table even if there is no match in
-- the departments table, and all rows are retrieved from the departments table
-- even if there is no match in the left table
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  FROM employees e FULL OUTER JOIN departments d
  ON (e.department_id = d.department_id);