SQL Joins - Displaying Data from Multiple Tables

SQL Joins - Displaying Data from Multiple Tables

Displaying Data from Multiple Tables

This session covers:

• Using SELECT statements to access data from more than one table using equality and non-equality joins.
• Viewing data that generally does not meet a join condition by using outer joins.
• Joining a table to itself.

Obtaining Data from Multiple Tables

Consider two tables, EMPLOYEES and DEPARTMENTS, with the following structure and sample data:

EMPLOYEES Table:

DEPARTMENTS Table:

Joining these tables on DEPARTMENT_ID would yield a combined dataset:

What Is a Join?

A join is used to query data from more than one table. The join condition is specified in the WHERE clause.

When the same column name appears in more than one table, prefix the column name with the table name to avoid ambiguity.

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

Cartesian Product

A Cartesian product occurs when:

• A join condition is omitted.
• A join condition is invalid.
• All rows in the first table are joined to all rows in the second table.

To avoid a Cartesian product, always include a valid join condition in the WHERE clause.

Generating a Cartesian Product

If EMPLOYEES table has 20 rows and DEPARTMENTS table has 8 rows, a Cartesian product will generate $20 \times 8 = 160$ rows.

A Cartesian product is generated if a join condition is omitted. The example displays employee name and department name from EMPLOYEES and DEPARTMENTS tables. Because no WHERE clause has been specified, all rows (20 rows) from the EMPLOYEES table are multiplied joined with all rows (8 rows) in the DEPARTMENTS table, thereby generating 160 rows in the output.

What Is an Equijoin?

Consider the following data in EMPLOYEES and DEPARTMENTS tables:

EMPLOYEES Table:

DEPARTMENTS Table:

DEPTNO in EMPLOYEES is a foreign key referencing DEPTNO in DEPARTMENTS, which is a primary key.

Retrieving Records with Equijoins

SELECT employee_id, last_name, e.department_id, d.location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;

This query joins the employees and departments tables where e.department_id equals d.department_id.

Qualifying Ambiguous Column Names

• Use table prefixes to qualify column names that are in multiple tables.
• Improve performance by using table prefixes.
• Distinguish columns that have identical names but reside in different tables by using column aliases.

Using Table Aliases

Simplify queries by using table aliases.

SELECT e.employee_id, e.last_name, d.department_no, d.location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;

SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;

Additional Search Conditions Using the AND Operator

To add more search conditions, use the AND operator in the WHERE clause.

Additional Search Conditions Using the AND Operator

SELECT e.employee_id, e.last_name, d.department_id, d.location_id
FROM EMPLOYEES E, DEPARTMENTS D
WHERE e.department_id = d.department_id
AND last_name = ‘KING’;

Joining More Than Two Tables

Consider three tables: CUSTOMER, ORDER, and ITEM.

CUSTOMER Table:

ORDER Table:

ITEM Table:

Joining More Than Two Tables…

SELECT Name, ItemId
FROM Customer C, Ord O, Item I
WHERE C.custId = O.custId
AND O.ordId = I.ordId;

Non-Equijoins

In a non-equijoin, the join condition uses operators other than equality (e.g., BETWEEN).

Consider EMP and SALGRADE tables:

EMP Table:

SALGRADE Table:

The salary in the EMP table is between the low salary and high salary in the SALGRADE table.

Retrieving Records with Non-Equijoins

SELECT e.last_name, e.salary, j.grade AS GRA
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

Outer Joins

Returning Records with No Direct Match with Outer Joins.

Consider EMPLOYEES and DEPARTMENTS tables. If there is no employee in the OPERATIONS department, an equijoin would not display the OPERATIONS department.

If a row does not satisfy a join condition, the row will not appear in the query result. For example, in the equijoin condition of EMPLOYEES and DEPARTMENTS tables, department OPERATIONS does not appear because no one works in that department!

Outer Joins

You use an outer join to also see rows that do not usually meet the join condition. The outer join operator is the plus sign (+). It is placed on the “side” of the join that is deficient in information.

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table2.column;

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2.column(+);

Using Outer Joins

SELECT e.last_name, d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
ORDER BY e.department_id;

This query displays numbers and names for all the departments, including the OPERATIONS department, which does not have any employees.

Using Outer Joins

Outer Join Restrictions:

• The outer join operator can appear on only one side of the expression—the side that has information missing. It returns those rows from one table that have no direct match in the other table.
• A condition involving an outer join cannot use the IN operator or be linked to another condition by the OR operator.

Self Joins

Sometimes you need to join a table to itself. To find the name of each employee’s manager, you need to join the EMP table to itself, or perform a self join!

Self Joins

Joining EMP table to itself to find managers:

EMP (WORKER)

EMP (MANAGER)

MGR in the WORKER table is equal to EMPNO in the MANAGER table.

Joining a Table to Itself (Self Joins)

SELECT e.last_name emp, m.last_name mgr
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

Summary

• Use SELECT statements to access data from more than one table: Equi-join and non-equi joins
• View data that generally does not meet a join condition by using outer joins
• Join a table to itself: self-joins