Displaying Data
from Multiple Tables
ObjectivesAfter completing this lesson, you should be able to
do the following:
• Write SELECT statements to access data from
more than one table using equality and
nonequality joins
• View data that generally does not meet a join
condition by using outer joins
• Join a table to itself by using a self join
Obtaining Data from Multiple Tables
EMPLOYEES DEPARTMENTS
Data from Multiple Tables
Sometimes you need to use data from more than one table. In the slide example, the report displays
data from two separate tables.
• Employee IDs exist in the EMPLOYEES table.
• Department IDs exist in both the EMPLOYEES and DEPARTMENTS tables.
• Location IDs exist in the DEPARTMENTS table.
To produce the report, you need to link the EMPLOYEES and DEPARTMENTS tables and access data
from both of them.
Cartesian Products
• A Cartesian product is formed 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 a WHERE clause.
Cartesian Products
When a join condition is invalid or omitted completely, the result is a Cartesian product, in which all
combinations of rows are displayed. All rows in the first table are joined to all rows in the second
table.
A Cartesian product tends to generate a large number of rows, and the result is rarely useful. You
should always include a valid join condition in a WHERE clause, unless you have a specific need to
combine all rows from all tables.
Cartesian products are useful for some tests when you need to generate a large number of rows to
simulate a reasonable amount of data.
Cartesian Products (continued)
A Cartesian product is generated if a join condition is omitted. The example on the slide displays
employee last name and department name from the EMPLOYEES and DEPARTMENTS tables.
Because no WHERE clause has been specified, all rows (20 rows) from the EMPLOYEES table are
joined with all rows (8 rows) in the DEPARTMENTS table, thereby generating 160 rows in the output.
SELECT last_name, department_name dept_name
FROM employees, departments;
Types of Joins
• Cross joins
• Natural joins
• Using clause
• Full or two sided outer
joins
• Arbitrary join conditions
for outer joins
• Equijoin
• Non-equijoin
• Outer join
• Self join
Joining Tables Using Oracle Syntax
Use a join to query data from more than one table.
• Write the join condition in the WHERE clause.
• Prefix the column name with the table name when
the same column name appears in more than one
table.
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
Defining Joins
When data from more than one table in the database is required, a join condition is used. Rows in one
table can be joined to rows in another table according to common values existing in corresponding
columns, that is, usually primary and foreign key columns.
To display data from two or more related tables, write a simple join condition in the WHERE clause.
In the syntax:
table1.column denotes the table and column from which data is retrieved
table1.column1 = is the condition that joins (or relates) the tables together
table2.column2
Equijoins
To determine an employee’s department name, you compare the value in the DEPARTMENT_ID
column in the EMPLOYEES table with the DEPARTMENT_ID values in the DEPARTMENTS table.
The relationship between the EMPLOYEES and DEPARTMENTS tables is an equijoin—that is, values
in the DEPARTMENT_ID column on both tables must be equal. Frequently, this type of join involves
primary and foreign key complements.
Note: Equijoins are also called simple joins or inner joins.
Columns to Display Originating Table Condition
last_name employees last_name=’Goyal’
department_name departments employees.department_id =
departments.department_id
Retrieving Records with Equijoins
In the slide example:
• The SELECT clause specifies the column names to retrieve:
– employee last name, employee number, and department number, which are
columns in the EMPLOYEES table
– department number, department name, and location ID, which are columns in
the DEPARTMENTS table
• The FROM clause specifies the two tables that the database must access:
– EMPLOYEES table
– DEPARTMENTS table
• The WHERE clause specifies how the tables are to be joined:
EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
Because the DEPARTMENT_ID column is common to both tables, it must be prefixed by
the table name to avoid ambiguity.
Additional Search Conditions
In addition to the join, you may have criteria for your WHERE clause to restrict the rows under
consideration for one or more tables in the join. For example, to display employee Matos’department
number and department name, you need an additional condition in the WHERE clause.
SELECT last_name, employees.department_id,
department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND last_name = ’Matos’;
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.
Qualifying Ambiguous Column Names
You need to qualify the names of the columns in the WHERE clause with the table name to avoid
ambiguity. Without the table prefixes, the DEPARTMENT_ID column could be from either the
DEPARTMENTS table or the EMPLOYEES table. It is necessary to add the table prefix to execute your
query.
If there are no common column names between the two tables, there is no need to qualify the columns.
However, using the table prefix improves performance, because you tell the Oracle Server exactly
where to find the columns.
The requirement to qualify ambiguous column names is also applicable to columns that may be
ambiguous in other clauses, such as the SELECT clause or the ORDER BY clause.
Using Table Aliases
• Simplify queries by using table aliases.
• Improve performance by using table prefixes.
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
Outer Joins Syntax
• You use an outer join to also see rows that do not
meet the join condition.
• The Outer join operator is the plus sign (+).
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(+);
Self Joins
Joining a Table to Itself
Sometimes you need to join a table to itself. To find the name of each employee’s manager, you need
to join the EMPLOYEES table to itself, or perform a self join. For example, to find the name of
Whalen’s manager, you need to:
• Find Whalen in the EMPLOYEES table by looking at the LAST_NAME column.
• Find the manager number for Whalen by looking at the MANAGER_ID column. Whalen’s
manager number is 101.
• Find the name of the manager with EMPLOYEE_ID 101 by looking at the LAST_NAME
column. Kochhar’s employee number is 101, so Kochhar is Whalen’s manager.
In this process, you look in the table twice. The first time you look in the table to find Whalen in the
LAST_NAME column and MANAGER_ID value of 101. The second time you look in the
EMPLOYEE_ID column to find 101 and the LAST_NAME column to find Kochhar.
Creating Cross Joins
• The CROSS JOIN clause produces the crossproduct
of two tables.
• This is the same as a Cartesian product between
the two tables.
SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;
Creating Cross Joins
The example on the slide gives the same results as the following:
SELECT last_name, department_name
FROM employees, departments;
Creating Natural Joins
• The NATURAL JOIN clause is based on all columns
in the two tables that have the same name.
• It selects rows from the two tables that have equal
values in all matched columns.
• If the columns having the same names have
different data types, an error is returned.
Retrieving Records with Natural Joins
SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations ;
Retrieving Records with Natural Joins
In the example on the slide, the LOCATIONS table is joined to the DEPARTMENT table by the
LOCATION_ID column, which is the only column of the same name in both tables. If other common
columns were present, the join would have used them all.
Equijoins
The natural join can also be written as an equijoin:
SELECT department_id, department_name,
departments.location_id, city
FROM departments, locations
WHERE departments.location_id = locations.location_id;
Natural Joins with a WHERE Clause
Additional restrictions on a natural join are implemented by using a WHERE clause. The example
below limits the rows of output to those with a department ID equal to 20 or 50.
SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations
WHERE department_id IN (20, 50);
Creating Joins with the USING Clause
• If several columns have the same names but the
data types do not match, the NATURAL JOIN
clause can be modified with the USING clause to
specify the columns that should be used for an
equijoin.
• Use the USING clause to match only one column
when more than one column matches.
• Do not use a table name or alias in the referenced
columns.
• The NATURAL JOIN and USING clauses are
mutually exclusive.
The USING Clause
Natural joins use all columns with matching names and data types to join the tables. The USING
clause can be used to specify only those columns that should be used for an equijoin. The columns
referenced in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL
statement.
For example, this statement is valid:
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;
This statement is invalid because the LOCATION_ID is qualified in the WHERE clause:
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE d.location_id = 1400;
ORA-25154: column part of USING clause cannot have qualifier
The same restriction applies to NATURAL joins also. Therefore columns that have the same name in
both tables have to be used without any qualifiers.
Retrieving Records with the USING Clause
SELECT e.employee_id, e.last_name, d.location_id
FROM employees e JOIN departments d
USING (department_id) ;
Creating Joins with the ON Clause
• The join condition for the natural join is basically
an equijoin of all columns with the same name.
• To specify arbitrary conditions or specify columns
to join, the ON clause is used.
• The join condition is separated from other search
conditions.
• The ON clause makes code easy to understand.
The ON Condition
Use the ON clause to specify a join condition. This lets you specify join conditions separate from any
search or filter conditions in the WHERE clause.
Retrieving Records with the ON Clause
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
Creating Joins with the ON Clause
The ON clause can also be used as follows to join columns that have different names:
SELECT e.last_name emp, m.last_name mgr
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id);
Creating Three-Way Joins with the ON
Clause
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
Additional Conditions
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149 ;
Summary
In this lesson, you should have learned how to use
joins to display data from multiple tables in:
No comments:
Post a Comment