Join , Sub queries and set
operators
Obtaining Data from Multiple Tables
EMPLOYEES DEPARTMENTS
…
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.
Generating a Cartesian
Product
EMPLOYEES (20 rows) DEPARTMENTS (8 rows)
Cartesian
product:
20 x 8 = 160
rows …
Types of Oracle-Proprietary
Joins
– Equijoin
– Nonequijoin
– Outer join
– Self-join
Joining Tables Using Oracle
Syntax
• Use a join to query data from more than one table:
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
– 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.
Qualifying Ambiguous
Column Names
– Use table prefixes to qualify column names that are
in multiple tables.
– Use table prefixes to improve performance.
– Instead of full table name prefixes, use table aliases.
– Table aliases give a table a shorter name.
• Keeps SQL code smaller, uses less memory
– Use column aliases to distinguish columns that have
identical names, but reside in different tables.
Equijoins
EMPLOYEES DEPARTMENTS
Primary key
Foreign key
Retrieving Records with
Equijoins
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;
…
Retrieving Records with Equijoins:
Example
SELECT d.department_id, d.department_name,
d.location_id, l.city
FROM departments d, locations l
WHERE d.location_id = l.location_id;
Additional Search Conditions
Using the AND Operator
SELECT d.department_id, d.department_name, l.city
FROM departments d, locations l
WHERE d.location_id = l.location_id
AND d.department_id IN (20, 50);
Joining More than Two Tables
EMPLOYEES DEPARTMENTS LOCATIONS
• To join n tables together, you need a
minimum of n–1
• join conditions. For example, to join three
tables, a
• minimum of two joins is required.
Nonequijoins
EMPLOYEES JOB_GRADES
JOB_GRADES table defines LOWEST_SAL
… and HIGHEST_SAL range of values for
each GRADE_LEVEL. Hence, the
GRADE_LEVEL column can be used to
assign grades to each employee.
Retrieving Records
with Nonequijoins
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
…
Returning Records with No Direct Match
with Outer Joins
DEPARTMENTS EMPLOYEES
There are no employees in
department 190.
Outer Joins: Syntax
– You use an outer join to 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(+);
Using Outer Joins
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id ;
…
Outer Join: Another Example
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+) ;
…
Joining a Table to Itself
EMPLOYEES (WORKER) EMPLOYEES (MANAGER)
… …
MANAGER_ID in the WORKER table is equal to
EMPLOYEE_ID in the MANAGER table.
Self-Join: Example
SELECT worker.last_name || ' works for '
|| manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
…
Obtaining Data from Multiple Tables
EMPLOYEES DEPARTMENTS
…
Creating Joins with the ON
Clause
– The join condition for the natural join is basically an equijoin
of all columns with the same name.
– Use the ON clause to specify arbitrary conditions or specify
columns to join.
– The join condition is separated from other search conditions.
– The ON clause makes code easy to understand.
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 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;
…
Applying Additional Conditions
to a Join
• Use the AND clause or the WHERE clause to apply 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 ;
Or
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)
WHERE e.manager_id = 149 ;
Joining a Table to Itself
EMPLOYEES (WORKER) EMPLOYEES (MANAGER)
… …
MANAGER_ID in the WORKER table is equal to
EMPLOYEE_ID in the MANAGER table.
Self-Joins Using the ON
Clause
SELECT worker.last_name emp, manager.last_name mgr
FROM employees worker JOIN employees manager
ON (worker.manager_id = manager.employee_id);
…
Returning Records with No Direct Match
with Outer Joins
DEPARTMENTS EMPLOYEES
There are no employees in
department 190.
LEFT OUTER JOIN
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
…
RIGHT OUTER JOIN
SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
…
FULL OUTER JOIN
SELECT e.last_name, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
…
Using a Subquery to Solve a
Problem
• Who has a salary greater than Abel’s?
Main query:
Which employees have salaries greater than
Abel’s salary?
Subquery:
What is Abel’s salary?
Subquery Syntax
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
– The subquery (inner query) executes before the main
query (outer query).
– The result of the subquery is used by the main query.
Using a Subquery
SELECT last_name, salary
11000
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = 'Abel');
Guidelines for Using
Subqueries
– Enclose subqueries in parentheses.
– Place subqueries on the right side of the comparison
condition for readability (However, the subquery can
appear on either side of the comparison operator.).
– Use single-row operators with single-row subqueries and
multiple-row operators with multiple-row subqueries.
Types of Subqueries
– Single-row subquery
Main query
returns
Subquery ST_CLERK
– Multiple-row subquery
Main query
returns ST_CLERK
Subquery
SA_MAN
Single-Row Subqueries
– Return only one row
– Use single-row comparison operators
Operator Meaning
= Equal to
> Greater than
>= Greater than or equal
< to
Less than
<= Less than or equal to
<> Not equal to
Executing Single-Row
Subqueries
SELECT last_name, job_id, salary
FROM employees
SA_REP
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = ‘Taylor’)
AND salary > 8600
(SELECT salary
FROM employees
WHERE last_name = ‘Taylor’);
Using Group Functions in a
Subquery
SELECT last_name, job_id, salary
FROM employees 2500
WHERE salary =
(SELECT MIN(salary)
FROM employees);
The HAVING Clause with Subqueries
– The Oracle server executes the subqueries first.
– The Oracle server returns results into the HAVING
clause of the main query.
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id 2500
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
…
What Is Wrong with This Statement?
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
Single-row operator
with multiple-row
subquery
No Rows Returned by the Inner
Query
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
Subquery returns no rows because there is no
employee named “Haas.”
Multiple-Row Subqueries
– Return more than one row
– Use multiple-row comparison operators
Operator Meaning
IN Equal to any member in the list
ANY Must be preceded by =, !=, >, <, <=, >=.
Compares a value to each value in a list or
returned by a query. Evaluates to FALSE if
the query returns no rows.
ALL Must be preceded by =, !=, >, <, <=, >=.
Compares a value to every value in a list
or returned by a query. Evaluates to TRUE
if the query returns no rows.
Using the ANY Operator
in Multiple-Row Subqueries
SELECT employee_id, last_name, job_id, salary
FROM employees 9000, 6000, 4200
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
…
Using the ALL Operator
in Multiple-Row Subqueries
SELECT employee_id, last_name, job_id, salary
FROM employees 9000, 6000, 4200
WHERE salary < ALL
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
Null Values in a Subquery
SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr);
A B
Set Operators
A B
UNION/UNION ALL
A B
INTERSECT
A B
MINUS
Set Operator Guidelines
– The expressions in the SELECT lists must match in number.
– The data type of each column in the second query must
match the data type of its corresponding column in the first
query.
– Parentheses can be used to alter the sequence of
execution.
– ORDER BY clause can appear only at the very end of the
statement.
The Oracle Server and Set
Operators
– Duplicate rows are automatically eliminated except in
UNION ALL.
– Column names from the first query appear in the result.
– The output is sorted in ascending order by default
except in UNION ALL.
UNION Operator
A B
The UNION operator returns rows from both queries after eliminating
duplications.
Using the UNION Operator
• Display the current and previous job details of all
employees. Display each employee only once.
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;
…
UNION ALL Operator
A B
The UNION ALL operator returns rows from both queries, including all
duplications.
Using the UNION ALL
Operator
• Display the current and previous departments of all employees.
SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history
ORDER BY employee_id;
…
INTERSECT Operator
A B
The INTERSECT operator returns rows that are common to both
queries.
Using the INTERSECT
Operator
• Display the employee IDs and job IDs of those employees
who currently have a job title that is the same as their
previous one (that is, they changed jobs but have now
gone back to doing the same job they did previously).
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;
MINUS Operator
A B
The MINUS operator returns all the distinct rows selected by the first
query, but not present in the second query result set.
Using the MINUS Operator
• Display the employee IDs of those employees who have not
changed their jobs even once.
SELECT employee_id
FROM employees
MINUS
SELECT employee_id
FROM job_history;
…
Matching the SELECT Statement:
Example
• Using the UNION operator, display the employee ID, job ID,
and salary of all employees.
SELECT employee_id, job_id,salary
FROM employees
UNION
SELECT employee_id, job_id,0
FROM job_history;
…
Using the ORDER BY Clause in Set
Operations
– The ORDER BY clause can appear only
once at the end of the compound
query.
– Component queries cannot have
individual ORDER BY clauses.
– ORDER BY clause recognizes only the
columns of the first SELECT query.
– By default, the first column of the first
SELECT query is used to sort the
output in an ascending order.
