SQL Chapter 4
Multiple-Table Queries

Objectives

Use joins to retrieve data from more than one table

Use the IN and EXISTS operators to query multiple tables

Use a subquery within a subquery

Use an alias

Join a table to itself

Perform set operations (union, intersection, and difference)

Use the ALL and ANY operators in a query

Perform special operations (inner join, outer join, and product)

Querying Multiple Tables

To retrieve data from two or more tables:

l Join the tables

l Formulate a query using the same commands as for single tables

Joining Two Tables

To retrieve data from more than one table, join the tables together

Tables can be joined by finding rows in the two tables that have identical values in matching columns

To join (relate) two or more tables, the SQL command is constructed as follows:

SELECT clause: list all columns you want to display

FROM clause: list all tables involved in the query

WHERE clause: list the condition that restricts the data to be retrieved to only those rows from the two tables that match

Qualifying column names is especially important when joining tables

Precede column name with the table name, followed by a period

Comparing of JOIN, IN, and EXISTS

Join tables by including a condition in the WHERE clause to ensure that matching columns contain equal values

Similar results can be obtained by using either the IN operator or the EXISTS operator with a subquery

Either approach obtains the same results

Comparing of JOIN, IN, and EXISTS

Problem:

l Find the description of each part included in order number 21610

Solution:

SELECT DESCRIPTION
FROM ORDER_LINE, PART
WHERE ORDER_LINE.PART_NUM = PART.PART_NUM
AND ORDER_NUM = '21610';

Using a Subquery Within a Subquery

When a subquery is within another subquery, it is called a nested subquery

SQL evaluates queries from the innermost to the outermost:

l Innermost subquery is evaluated first

l Next (intermediate) subquery is evaluated

l Outer query is evaluated last

A Comprehensive Example

The example given in this section involves several of the features already discussed

Problem:

l List the customer number, order number, order date, and order total for each order with a total that exceeds $1,000. Rename the order total as ORDER_TOTAL

Using an Alias

Tables listed in the FROM clause can be given an alternative name

An alias is created by:

l Typing the name of the table

l Pressing the space bar

l Typing the name of the alias

One reason for using an alias is simplicity

Joining a Table to Itself

A second reason for using an alias is that it is needed when joining a table to itself, called a self-join

Problem:

l For each pair of customers located in the same city, display the customer number, customer name, and city

Joining Several Tables

It is possible to join several tables

For each pair of tables, you must include a condition indicating how the columns are related

The procedure for joining more than two tables is essentially the same as the one for joining two tables

Joining Several Tables

In such a query:

l The condition in the WHERE clause will be a compound condition

l All the desired columns would be listed in the SELECT clause

l Any columns that appear in more than one table would be qualified

l In the FROM clause, the tables that are involved in the query would be listed

Set Operations

The union of two tables is a table containing every row that is in either the first table, the second table, or both tables

The intersection (intersect) of two tables is a table containing all rows that are in both tables

The difference (minus) of two tables is the set of all rows that are in the first table but that are not in the second table

The two tables in the union must be “union compatible”

Two tables are union compatible if:

They have the same number of columns

Their corresponding columns have identical data types and lengths

ALL and ANY

ALL and ANY operators are used with subqueries to produce a single column of numbers

l ALL: condition is true only if it satisfies all values produced by the subquery

l ANY: condition is true if it satisfies any value (one or more) produced by the subquery

Special Operations

The special operations within SQL are:

l Self-join

l Inner join

l Outer join

l Product

Inner Join

A join that compares the tables in the FROM clause and lists only those rows that satisfy the condition in the WHERE clause is called an inner join

The joins mentioned in this text so far have been inner joins

Outer Join

Sometimes, there is a need to list all the rows from one of the tables in a join, regardless of whether they match any rows in the other table

This type of join is called an outer join

There are three types of outer joins:

Left outer join - all rows from the table on the left will be included

Right outer join - all rows from the table on the right will be included

Full outer join - all rows from both tables will be included

Product

The product is formally called the Cartesian Product

The product of two tables is the combination of all rows in the first table and all rows in the second table

Summary

To retrieve data from more than one table, you must join the tables together

The IN or EXISTS operators can be used as an alternate way of performing a join

A subquery can contain another subquery

The UNION command creates a union of two tables (the collection of rows that are in either or both tables)

The INTERSECT command creates the intersection of two tables (the collection of rows that are in both tables)

The MINUS command creates the difference of two tables

In an inner join, only matching rows from both tables are included

In a left outer join, all rows from the table on the left will be included

In a right outer join, all rows from the table on the right will be included

The product of two tables is the combination of all rows in the first table and all rows in the second table

SQL Project Four Completed

Good Luck

H. Zamanzadeh