MODULE OF INSTRUCTION
Advanced SQL
Welcome to the first module of this course on the Advanced Database
Systems! Understanding of SQL and its capabilities for querying a
single table is a pre-requisite.
In this module, you will learn how to retrieve data from multiple tables
using one SQL statement. You will see how tables can be joined
together and how similar results are obtained using different
approaches, including the joins and subqueries.
It is important that you understand how to query multiple tables for
generating approporiate reports in the creation of an information
system.
Learning Objectives
After studying this lesson, you should be able to:
• Concisely define key terms.
• Write single and multiple table queries using SQL commands.
• Define types of join commands and use SQL to write these
commands.
• Write single-row and multiple-row subqueries.
• Write correlated subqueries and know when to write them.
Joining More Than One Tables
You already learned from your previous database course how to
generate reports from a single table. This time we will explore the
capabilities of SQL in querying multiple tables by joining these tables.
Join can be performed by combining two or more tables together by
finding rows that match the values in the common columns. These
common columns in joined tables are usually the primary key of the
parent table and the foreign key of the child table in a one-to-many
relationships.
Advanced Database Systems 1
1.0 Advanced SQL
A join can either be done implicitly or explicitly. Implicit join
is performed by referring in a WHERE clause to the matching
of columns over which tables were joined. On the other hand,
JOIN…ON commands are included in the FROM clause to
join tables explicitly. Various types of join are described in the
following sections.
Equi-join
Equi-join is a type of join that selects rows from the two tables
that have equal values in the common columns, which appear
redundantly in the result table.
For example, if you want to know the names of the students
who have enrolled for courses, that information are kept in two
tables, Student (Table 1.1) and Enrollment (Table 1.2). It is
necessary to match the student names with their courses in
order to answer the question.
Table 1.1 Student
Table 1.2 Enrollment
2
MODULE OF INSTRUCTION
Query: What are the id numbers and last names of all students, along
with their course codes for all the courses they have enrolled?
Implicit:
Use table prefixes to Explicit:
qualify column
names that are in
multiple tables.
Result:
Use column aliases
to distinguish
columns that have
identical names, but
reside in different
tables.
Natural Join
A natural join is similar to equi-join, however, it is performed over
matching columns, and one of the duplicate columns is eliminated in
the result table.
Query: What are the id numbers and last names of all students, along
with their course codes for all the courses they have enrolled?
This join can only be used on all columns that have the same names
and data types in both tables. Otherwise, an error is returned.
The result table is the same as the equi-join.
Advanced Database Systems 3
1.0 Advanced SQL
Outer Join
In SQL:1999, the joined table that returns only matched rows is
called an INNER join. Both equi-join and natural join performs
an inner join of two tables.
Sometimes you also would want to retrieve rows that do not
have matching values in the common columns. This join is
known as the OUTER join. Outer join can be left, right, or full
outer join. A join between two tables that returns the matched
and unmatched rows from the left (or right) table is called a left
(or right) outer join. Likewise, joining two tables that returns
the results of the inner join as well as the results of the left and
right outer join is a full outer join.
Query: List all the student id, name and course code for all the
students listed in the Student table. Include also the student
who did no take any course.
Result:
Notice that the query retrieves all the rows in the Student table,
which is the left table, even if there is no match in the
Enrollment table. Consequently, a null value in the column of
the Enrollment table indicates that no match is achieved.
4
MODULE OF INSTRUCTION
Table 1.3 Course
Query: List all the student id and course code for all the courses listed
in the Course table (Table 1.3). Include the course code even if there is
no student enrolled in it.
Result:
The query retrieves all the rows in the Course table, which is the right
table, even if there is no match in the Enrollment table. Notice also
that the outer join indicates null as a value for the student id column
where no match is found.
Query: List the student id, name, course code and description for all
students and courses in the Student and Course Table. Include the rows
even if there is no data available.
Advanced Database Systems 5
1.0 Advanced SQL
Result:
Notice the null values on the columns with no match rows in the related
tables.
Self Join
Sometimes it is necessary to join a table to itself. This
operation is known as self join or recursive join.
It is common in unary relationship, such as the Manages
relationship as shown in Figure 1.1.
Figure 1.1 Unary relationship
Table 1.4 Employee
6
MODULE OF INSTRUCTION
In Table 1.4, the employee_id is the primary key and manager_id is
the foreign key.
Query: Find the names of the employees, along with their managers in
the Employee table.
Use table aliases in It is necessary to use a table alias when joining a table to itself to avoid
performing a self ambinguity. Otherwise, an error is returned since you are using the
join. same table in the query.
Result:
Subqueries
The previous SQL examples illustrate one of the approaches for
processing multiple tables. SQL also provides a powerful technique to
obtain values based on unknown conditional value by using
subqueries. A subquery is a SELECT statement (inner query) that is
embedded in the WHERE or HAVING clause of another SELECT
statement (outer query). The inner query executes first and returns a
value that is used by the outer query.
Two classes of comparison operators are used in the subqueries: singe-
row operators and multiple-row operators.
Advanced Database Systems 7
1.0 Advanced SQL
Single-row Subquery
In single-row subqueries, only one row is returned by the inner
query. This type of subquery uses a single-row operator.
Table 1.5 Single-row operators
Use single-row
operators if the
result of the inner
query returns only
one row.
Table 1.6 Emp
Table 1.6 shows the records of the Emp table, which will be
used in the subsequent examples.
Query: Display the employees along with their salary whose
salary is less than Hunold’s salary.
8
MODULE OF INSTRUCTION
Result:
Multiple-row Subquery
Multiple-row suqueries return more than row and uses a multiple-row
operator, which expects one or more values from the inner query.
Table 1.7 Multiple-row operators
Use multiple-row
operators if the
result of the inner
query returns more
than row.
Query: Find the employees who earn the same salary as the total salary
of all employees in each job.
Result:
Advanced Database Systems 9
1.0 Advanced SQL
Query: Find the employees whose salary is the same as the
salary of any employees with job id of IT_PROG and whose
job is not IT_PROG.
Result:
Query: Find the employees whose salary is greater than the
salary of all employees with job id of IT_PROG and whose job
is not IT_PROG.
Result:
Correlated Subquery
In the preceding subquery examples, you must examine the
results of the inner query before considering the outer query.
On the contrary, correlated subqueries use the results of the
outer query in processing the inner query. You can use the
EXISTS operator to process correlated subquery.
10
MODULE OF INSTRUCTION
Query: Find the student id and name of the students who enrolled in
the course MIT101.
Result:
You can also process this multiple table query using the IN operator or
the join SQL statement. You will get the same results.
Activities/Exercises
Answer briefly the following questions:
1 When is an outer join used instead of an inner join?
2 Explain the processing order of a correlated subquery.
3 Explain the following statement regarding SQL: Any query that
can be written using the subquery approach can also be written
using the joining approach but not vice versa.
Advanced Database Systems 11
1.0 Advanced SQL
Glossary
Correlated subquery
- A subquery in which processing the inner query depends on
data from the outer query.
Equi-join
- A join in which the joining condition is based on equality
between values in the common columns. Common columns
appear (redundantly) in the result table.
Join
- A relational operation that causes two tables with a
common domain to be combined into a single table.
Natural join
- A join that is the same as equijoin except that one of the
duplicate columns is eliminated in the result table.
Outer join
- A join in which rows that do not have matching values in
common columns are nevertheless included in the result
table.
Subquery
- A query (inner query) within another query (outer query).
References
Hoffer, J., Ramesh, V., Topi, H. (2013). Modern Database
Management, 11th Ed. New Jersey: Pearson Education, Inc.
Price, J. (2008). Oracle Database 11g SQL: Master SQL and
PL/QL in the Oracle Database. New York: McGraw-Hill.
12
