a guide to sql, seventh edition
DESCRIPTION
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 - PowerPoint PPT PresentationTRANSCRIPT
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
A Guide to SQL, Seventh Edition
A Guide to SQL, Seventh Edition
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
A Guide to SQL, Seventh Edition
A Guide to SQL, Seventh Edition
A Guide to SQL, Seventh Edition
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
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
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
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
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