A Guide to SQL, Seventh Edition

A Guide to SQL, Seventh Edition

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 A Guide to SQL, Seventh Edition

Objectives • 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) A Guide to SQL, Seventh Edition

Querying Multiple Tables • When querying more than one table, the tables must be joined • Join tables by finding columns with matching data • Join tables by using a condition in the WHERE clause A Guide to SQL, Seventh Edition

Joining Two Tables • In the SELECT clause, list all columns you want to display • In the FROM clause, list all tables involved in the query • In the WHERE clause, restrict to the rows that have common values in matching columns A Guide to SQL, Seventh Edition

Comparing JOIN, IN, and EXISTS • Tables can be joined using IN or EXISTS clause • Use IN operator with a subquery • Use the EXISTS operator to retrieve data from more than one table A Guide to SQL, Seventh Edition

Correlated Subquery • Subquery involves a table listed in the outer query • In Figure 4.7 the ORDERS table, listed in the FROM clause of the outer query, is used in the subquery • You need to qualify ORDER_NUM column in subquery as ORDERS.ORDER_NUM A Guide to SQL, Seventh Edition

Correlated Subquery • For each row in the ORDERS table • Subquery executed using the value of ORDERS.ORDER_NUM that appears in the row • The inner query makes a list of rows in the ORDER_LINE table • Where ORDER_LINE.ORDER_NUM matches this value and • In which PART_NUM is equal to DR93 A Guide to SQL, Seventh Edition

Using a Subquery within a Subquery • A nested subquery is a subquery within a subquery • SQL evaluates the queries from the innermost query to the outermost • It is possible that there is more than one approach to formulation of the queries • Many DMBS have optimizers that analyze queries for efficiency A Guide to SQL, Seventh Edition

Using an Alias • An alias is an alternate name for a table • Used when tables are listed in the FROM clause • Created by typing the name of the table, hitting a space, then typing the name of the alias • Allows for simplicity A Guide to SQL, Seventh Edition

Joining a Table to Itself • A self-join is when you are joining a table to itself • A second use for using an alias • Used when comparing records within one table • Alias allows you to treat one table as two separate tables A Guide to SQL, Seventh Edition

Using a Self-Join on a Primary Key • It is possible to create a self-join that involves the primary key of the table • Just as in previous examples, you would list the table twice in the FROM clause with aliases A Guide to SQL, Seventh Edition

Joining Several Tables • Condition shows how the columns are related for each pair of tables A Guide to SQL, Seventh Edition

Joining Several Tables Step-By-Step • In the SELECT clause list all the columns to display • Qualify the column name if needed • In the FROM clause list all tables • Include tables used in the WHERE clause, even if they are not in the SELECT clause A Guide to SQL, Seventh Edition

Joining Several Tables Step-By-Step • Take one pair of related tables at a time • Indicate in the WHERE clause the condition that relates the tables • Join conditions with the AND operator • Include any additional conditions in the WHERE clause • Connect them with the AND operator A Guide to SQL, Seventh Edition

Set Operations • Set operations are used for taking the union, intersection, and differences of two tables • The union of two tables is a table containing every row that is in either the first table, the second table, or both tables A Guide to SQL, Seventh Edition

Set Operations • 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 tables but are not in the second table A Guide to SQL, Seventh Edition

Restrictions to Set Operations • Requirements for tables to be union compatible • Have the same number of columns AND • Their corresponding columns have identical data types and lengths A Guide to SQL, Seventh Edition

Special Operations • Inner Join • A join that compares the tables in the FROM clause and lists on those rows that satisfy the condition in the WHERE clause • Outer Join • A join that lists all the rows from one of the tables in a join, regardless of matching A Guide to SQL, Seventh Edition

Outer Joins • Left outer join: all rows from the table on the left (listed first in the query) will be included; matching rows only from the table on the right will be included • Right outer join: all rows from the table on the right will be included; matching rows only from the table on the left will be included • Full outer join: all rows from both tables will be included regardless of matches A Guide to SQL, Seventh Edition

Product • The product (Cartesian Product) of two tables is the combination of all rows in the first table and all rows in the second table • Omit the WHERE clause to form a product A Guide to SQL, Seventh Edition

Summary • Join tables with various methods • A subquery can contain another subquery • An alias can be used to simplify the SQL command as well to create self join • UNION, INTERSECT, MINUS commands are introduced • To form a product of two tables, include both tables in the FROM clause and omit the WHERE clause A Guide to SQL, Seventh Edition

A Guide to SQL, Seventh Edition