Transcript Multiple Table Queries Part 2 - Fox Valley Technical College

Multiple Table Queries Part 3
1
Before starting to write your query
what are some good questions to ask
yourself?
- Which tables contain the
information I need?
- How are the tables related
to one another?
2
Instructor Table
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class Table
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
3
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Results
Using a subquery, list the names
of instructors who teach a SQL
class.
select
name as 'Instructor Name'
from
instructor
where
instructor_id in
(
select
instructor_id
from
class
where
name = 'SQL'
)
Instructor Name
Glen Orsburn
Kris Windorski
4
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Results
Using a subquery, list the names
of instructors who do not teach a
SQL class.
select
name as 'Instructor Name'
from
instructor
where
instructor_id not in
(
select
instructor_id
from
class
where
name = 'SQL'
)
Instructor Name
Doug Waterman
Terri Keane
5
Instructor
Correlated Subquery using “exists”
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Results
Instructor Name
Glen Orsburn
Kris Windorski
select
name as 'Instructor Name'
from
instructor i
where
exists
(
select
The join between the
subquery and outer
*
query makes
from
it a correlated subquery
class c
where
i.instructor_id = c.instructor_id and
c.name = 'SQL'
)
6
Instructor
Correlated Subquery using “not exists”
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Results
Instructor Name
Doug Waterman
Terri Keane
select
name as 'Instructor Name'
from
instructor i
where
not exists
(
select
*
from
class c
where
i.instructor_id = c.instructor_id and
c.name = 'SQL'
)
7
Return names of students who have cell
phones
Student_Names
select
sn.first_name,
sn.last_name
from
student_names sn
where
exists
(
select
Student_Phones
*
from
student_phones cell
where
cell.phone_type = 'cell'
)
Results
Given the data we have the
subquery will always return
something, so the existence
check will always prove true.
Need a correlated subquery.
Return names of students who have cell
phones
Student_Names
select
sn.first_name,
sn.last_name
from
student_names sn
where
exists
(
select
Student_Phones
*
from
student_phones cell
where
cell.student_id = sn.student_id and
cell.phone_type = 'cell'
)
Results
Return names of students who have cell
phones
Student_Names
select
sn.first_name,
sn.last_name
from
student_names sn
where
exists
(
select
Student_Phones
*
from
student_phones cell
where
cell.student_id = sn.student_id and
cell.phone_type = 'cell'
)
Results
Now with the correlated
subquery we get back only
those students with a cell
phone
Department Table
Dept_ID Name
1
IT
2
Adjunct
Instructor Table
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class Table
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
11
List the names of the
departments offering a SQL class
Department
Dept_ID Name
1
IT
2
Adjunct
select
Instructor
d.name as 'Department Name'
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
from
department d
join
instructor i on
i.dept_id = d.dept_id
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
join
class c on
c.instructor_id = i.instructor_id and
c.name = 'SQL'
Results
Department Name
Adjunct
IT
Adjunct
12
List the names of the
departments offering a SQL class
Department
Dept_ID Name
1
IT
2
Adjunct
select distinct
d.name as 'Department Name'
from
department d
join
instructor i on
i.dept_id = d.dept_id
join
class c on
c.instructor_id = i.instructor_id
where
c.name = 'SQL'
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Results
Department Name
Adjunct
IT
13
List the names of the
departments offering a SQL class
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Results
Department Name
Adjunct
IT
select
d.name as 'Department Name'
from
department d
where
d.dept_id in
(
select
i.dept_id
from
instructor i
where
i.instructor_id in
(
select
c.instructor_id
from
class c
where
c.name = 'SQL'
)
)
14
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Nested Subqueries
select
d.name as 'Department Name'
from
department d
where
d.dept_id in
(
select
i.dept_id
from
instructor i
where
i.instructor_id in
(
select
c.instructor_id
from
class c
where
c.name = 'SQL'
)
)
15
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Nested Subqueries
select
d.name as 'Department Name'
from
department d
where
d.dept_id in
(
select
i.dept_id
from
instructor i
where
i.instructor_id in
(
select
c.instructor_id
from
class c
where
c.name = 'SQL'
)
)
subquery results
4, 3, 4
16
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Nested Subqueries
select
d.name as 'Department Name'
from
department d
where
d.dept_id in
(
select
i.dept_id
from
instructor i
where
i.instructor_id in (4, 3, 4)
(
select
c.instructor_id
from
class c
where
c.name = 'SQL'
)
)
subquery results
1, 2
subquery results
4, 3, 4
17
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Nested Subqueries
select
d.name as 'Department Name'
from
department d
where
d.dept_id in (1, 2)
(
select
i.dept_id
from
instructor i
where
i.instructor_id in (4, 3, 4)
(
select
c.instructor_id
from
class c
where
c.name = 'SQL'
)
)
query results
IT, Ajunct
subquery results
1, 2
subquery results
4, 3, 4
18
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Nested Subqueries
select
d.name as 'Department Name'
from
department d
where
d.dept_id in
(
select
i.dept_id
from
instructor i
where
i.instructor_id in
(
select
c.instructor_id
from
class c
where
c.name = ‘Accounting'
)
)
19
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Nested Subqueries
select
d.name as 'Department Name'
from
department d
where
d.dept_id in
(
select
i.dept_id
from
instructor i
where
i.instructor_id in
(
select
c.instructor_id
from
class c
where
c.name = ‘Accounting'
)
)
subquery results
4
20
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Nested Subqueries
select
d.name as 'Department Name'
from
department d
where
d.dept_id in
(
select
i.dept_id
from
instructor i
where
i.instructor_id in (4)
(
select
c.instructor_id
from
class c
where
c.name = ‘Accounting'
)
)
subquery results
2
subquery results
4
21
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Nested Subqueries
select
d.name as 'Department Name'
from
department d
where
d.dept_id in (2)
(
select
i.dept_id
from
instructor i
where
i.instructor_id in (4, 3, 4)
(
select
c.instructor_id
from
class c
where
c.name = ‘Accounting'
)
)
query results
Ajunct
subquery results
2
subquery results
4
22
Joining a table to itself (self join)
Employees Table
Employee_ID
Name
Manager_ID
1
Kris Windorski
null
2
Bill Gates
1
3
Steve Jobs
1
4
Will Ferrell
1
5
Vince Vaughn
4
6
Owen Wilson
4
23
List the names of Kris Windorski’s
direct reports
Employees
Employee_ID
Name
Manager_ID
1
Kris Windorski
null
2
Bill Gates
1
3
Steve Jobs
1
4
Will Ferrell
1
5
Vince Vaughn
4
6
Owen Wilson
4
select
name
from
employees
join
employees on
employee_id = manager_id and
name = 'Kris Windorski'
Results
ERROR!!!
Need to qualify columns
24
List the names of Kris Windorski’s
direct reports
Employees
Employee_ID
Name
Manager_ID
1
Kris Windorski
null
2
Bill Gates
1
3
Steve Jobs
1
4
Will Ferrell
1
5
Vince Vaughn
4
6
Owen Wilson
4
Results
Name
Kris Windorski
Kris Windorski
Kris Windorski
select
m.name
from
employees e
join
employees m on
m.employee_id = e.manager_id and
m.name = 'Kris Windorski'
Pulling the name from the instance of
the employees table (aliased ‘m’)
representing managers
25
List the names of Kris Windorski’s
direct reports
Employees
Employee_ID
Name
Manager_ID
1
Kris Windorski
null
2
Bill Gates
1
3
Steve Jobs
1
4
Will Ferrell
1
5
Vince Vaughn
4
6
Owen Wilson
4
Results
Name
Bill Gates
Steve Jobs
Will Ferrell
select
e.name
from
employees e
join
employees m on
m.employee_id = e.manager_id and
m.name = 'Kris Windorski'
26
Staff Table
staff_id
1
2
3
4
staff_name
staff_address
Kris Windorski 1111 Michigan St Appleton, WI
Jessica Alba 1111 Michigan St Appleton, WI
Chuck Norris
4567 Main St Neenah, WI
Bruce Willis
1234 Circle Ct Menasha, WI
Student Table
student_id student_name
student_address
1
Bruce Willis
1234 Circle Ct Menasha, WI
2
Angelina Jolie 2121 Pitt Street Little Chute, WI
3
George Clooney 9876 Mason St Grand Chute, WI
27
Staff
staff_id
1
2
3
4
Create a 2 column report listing
names and addresses of staff
and students (to be used for a
mailing list)
staff_name
staff_address
Kris Windorski 1111 Michigan St Appleton, WI
Jessica Alba 1111 Michigan St Appleton, WI
Chuck Norris
4567 Main St Neenah, WI
Bruce Willis
1234 Circle Ct Menasha, WI
Student
student_id student_name
student_address
1
Bruce Willis
1234 Circle Ct Menasha, WI
2
Angelina Jolie 2121 Pitt Street Little Chute, WI
3
George Clooney 9876 Mason St Grand Chute, WI
select
staff_name,
staff_address
from
staff
will return a result
set of names and
addresses of staff
select
student_name,
student_address
from
student
will return a result
set of names and
addresses of
students
How do I combine the two result sets into a single query returning only 2 columns
28
Staff
staff_id
1
2
3
4
staff_name
staff_address
Kris Windorski 1111 Michigan St Appleton, WI
Jessica Alba 1111 Michigan St Appleton, WI
Chuck Norris
4567 Main St Neenah, WI
Bruce Willis
1234 Circle Ct Menasha, WI
Student
student_id student_name
student_address
1
Bruce Willis
1234 Circle Ct Menasha, WI
2
Angelina Jolie 2121 Pitt Street Little Chute, WI
3
George Clooney 9876 Mason St Grand Chute, WI
Results
staff_name
Kris Windorski
Jessica Alba
Chuck Norris
Bruce Willis
Angelina Jolie
George Clooney
staff_address
1111 Michigan St Appleton, WI
1111 Michigan St Appleton, WI
4567 Main St Neenah, WI
1234 Circle Ct Menasha, WI
2121 Pitt Street Little Chute, WI
9876 Mason St Grand Chute, WI
Create a 2 column report listing
names and addresses of staff
and students (to be used for a
mailing list)
select
staff_name,
staff_address
from
staff
union
select
student_name,
student_address
from
student
Note:
- query used the column
names from the first query
- duplicates were removed
29
Staff
staff_id
1
2
3
4
staff_name
staff_address
Kris Windorski 1111 Michigan St Appleton, WI
Jessica Alba 1111 Michigan St Appleton, WI
Chuck Norris
4567 Main St Neenah, WI
Bruce Willis
1234 Circle Ct Menasha, WI
Student
student_id student_name
student_address
1
Bruce Willis
1234 Circle Ct Menasha, WI
2
Angelina Jolie 2121 Pitt Street Little Chute, WI
3
George Clooney 9876 Mason St Grand Chute, WI
Results
Name
Kris Windorski
Jessica Alba
Chuck Norris
Bruce Willis
Angelina Jolie
George Clooney
Address
1111 Michigan St Appleton, WI
1111 Michigan St Appleton, WI
4567 Main St Neenah, WI
1234 Circle Ct Menasha, WI
2121 Pitt Street Little Chute, WI
9876 Mason St Grand Chute, WI
Create a 2 column report listing
names and addresses of staff
and students (to be used for a
mailing list)
select
staff_name as ‘Name’,
staff_address as ‘Address’
from
staff
union
select
student_name,
student_address
from
student
30
Staff
staff_id
1
2
3
4
staff_name
staff_address
Kris Windorski 1111 Michigan St Appleton, WI
Jessica Alba 1111 Michigan St Appleton, WI
Chuck Norris
4567 Main St Neenah, WI
Bruce Willis
1234 Circle Ct Menasha, WI
Student
student_id student_name
student_address
1
Bruce Willis
1234 Circle Ct Menasha, WI
2
Angelina Jolie 2121 Pitt Street Little Chute, WI
3
George Clooney 9876 Mason St Grand Chute, WI
Results
Name
Kris Windorski
Jessica Alba
Chuck Norris
Bruce Willis
Bruce Willis
Angelina Jolie
George Clooney
Address
1111 Michigan St Appleton, WI
1111 Michigan St Appleton, WI
4567 Main St Neenah, WI
1234 Circle Ct Menasha, WI
1234 Circle Ct Menasha, WI
2121 Pitt Street Little Chute, WI
9876 Mason St Grand Chute, WI
Create a 2 column report listing
names and addresses of staff
and students (to be used for a
mailing list)
select
staff_name as ‘Name’,
staff_address as ‘Address’
from
staff
union all
select
student_name,
student_address
from
student
“union all” returns duplicates
31
union
• Tables must be union compatible, that is, same
number of columns and corresponding
columns have identical data types and lengths
32
ALL and ANY
• ALL operator: condition is true only if it satisfies all
values
• ANY operator: condition is true only if it satisfies any
value
• Precede subquery with appropriate operator
A Guide to MySQL
33
Car_Dealer Table
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
Sales Table
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
34
List the names of cars with a cost greater
then the cost of all cars sold by Kris
Windorski
Car_Dealer
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
Sales
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Results
Car
Ferrari
Lexus
Car_Dealer_ID
1
1
3
4
4
2
2
select
car
from
sales
where
cost > all
(
select
cost
from
sales s
join
car_dealer c on
c.car_dealer_id = s.car_dealer_id and
c.name = 'Kris Windorski'
)
35
List the names of cars with a cost greater
then the cost of any cars sold by Kris
Windorski
Car_Dealer
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
Sales
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Results
Car
Cavalier
BMW
Ferrari
Lexus
Car_Dealer_ID
1
1
3
4
4
2
2
select
car
from
sales
where
cost > any
(
select
cost
from
sales s
join
car_dealer c on
c.car_dealer_id = s.car_dealer_id and
c.name = 'Kris Windorski'
)
36
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
What will the following
query return?
Results
Instructor Name
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Class Name
SQL
SQL
SQL
SQL
VB.net
VB.net
VB.net
VB.net
SQL
SQL
SQL
SQL
C++
C++
C++
C++
Accounting
Accounting
Accounting
Accounting
SQL
SQL
SQL
SQL
select
i.name as 'Instructor Name',
c.name as 'Class Name'
from
instructor i,
class c
A join is not specified for the
tables
SQL returns every possible
combination
Referred to as a Cartesian
Product
37
Instructor
Class
Instructor_ID
Name
Class_NBR Instructor_ID
Name
1
Doug Waterman
1111
4
SQL
2
Terri Keane
4444
1
VB.net
3
Glen Orsburn
2222
3
SQL
4
Kris Windorski
5555
2
C++
5
Bill Gates
99999
4
Accounting
7777
4
SQL
List the names of instructors and
the classes they teach. List
instructors regardless if they are
teaching a class or not
select distinct
i.name as 'Instructor Name',
c.name as 'Class Name'
from
instructor i
join
class c on
c.instructor_id = i.instructor_id
Results
Instructor Name
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Kris Windorski
Class Name
VB.net
C++
SQL
SQL
Accounting
Is this what we want?
NO, Bill Gates is missing.
We want all instructors regardless if
they are teaching a class or not
38
Instructor
Class
Instructor_ID
Name
Class_NBR Instructor_ID
Name
1
Doug Waterman
1111
4
SQL
2
Terri Keane
4444
1
VB.net
3
Glen Orsburn
2222
3
SQL
4
Kris Windorski
5555
2
C++
5
Bill Gates
99999
4
Accounting
7777
4
SQL
select distinct
i.name as 'Instructor Name',
c.name as 'Class Name'
from
instructor i
left outer join
class c on
c.instructor_id = i.instructor_id
Results
Instructor Name
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Kris Windorski
Bill Gates
List the names of instructors and
the classes they teach. List
instructors regardless if they are
teaching a class or not
Class Name
VB.net
C++
SQL
SQL
Accounting
null
Left outer join:
Returns data from the table on the left regardless if
it has a match in the table on the right. If there is
not a match on the right, null values are returned.
39
Class
Instructor
Class_NBR Instructor_ID
Name
Instructor_ID
Name
1111
4
SQL
1
Doug Waterman
4444
1
VB.net
2
Terri Keane
2222
3
SQL
3
Glen Orsburn
5555
2
C++
4
Kris Windorski
99999
4
Accounting
5
Bill Gates
7777
4
SQL
select distinct
i.name as 'Instructor Name',
c.name as 'Class Name'
from
class c
Results
Instructor Name
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Kris Windorski
Bill Gates
Class Name
VB.net
C++
SQL
SQL
Accounting
null
List the names of instructors and
the classes they teach. List
instructors regardless if they are
teaching a class or not
right outer join
instructor i on
c.instructor_id = i.instructor_id
right outer join:
Returns data from the table on the right regardless
if it has a match in the table on the left. If there is
not a match on the left, null values are returned.
40
Car_Dealer
Sales
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
What will the following query return?
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id
Results
name
Kris Windorski
Kris Windorski
Al Pacino
Al Pacino
Paris Hilton
Jessica Simpson
Jessica Simpson
A.J. Hawk
car
Porsche
Cavalier
Ferrari
Lexus
Mustang
Vibe
BMW
null
41
Car_Dealer
Sales
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
What will the following query return?
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id and
s.cost > 50000
Results
name
Kris Windorski
Al Pacino
Al Pacino
Paris Hilton
Jessica Simpson
A.J. Hawk
car
Cavalier
Ferrari
Lexus
null
null
null
42
Car_Dealer
Sales
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
What will the following query return?
select
name,
car
from
car_dealer c
Results
name
Kris Windorski
Al Pacino
Al Pacino
car
Cavalier
Ferrari
Lexus
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id
where
s.cost > 50000
43
Why do these queries produce different results?
select
name,
car
from
car_dealer c
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id and
s.cost > 50000
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id
where
s.cost > 50000
When a query only has inner joins a filter placed in the “where”
clause will yield the same result as placing the filter in the “from”
clause.
However, the same cannot be said when dealing with outer joins
Let’s take a closer look
44
Car_Dealer
Sales
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id and
s.cost > 50000
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
A left outer join returns data from the table on
the left regardless if it has a match in the table
on the right.
In this query in order for there to be a match
between the two tables the join criteria must
be met, which includes the following :
- Matching car dealer ids
- A cost greater then 50,000
45
Car_Dealer
Sales
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
select
name,
car
from
car_dealer c
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
The car_dealer table is the table on the left, so we know every car
dealer’s name will be returned regadless if it satisfies the join
criteria
Results
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id and
s.cost > 50000
name
Kris Windorski
Al Pacino
Al Pacino
Paris Hilton
Jessica Simpson
A.J. Hawk
car
Cavalier
Ferrari
Lexus
null
null
null
46
Car_Dealer
Sales
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
select
name,
car
from
car_dealer c
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
If the join condition is satisfied the information from the right (in
this case the sales table) will also be returned. If the condition is
not met null values will be returned from the right.
Results
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id and
s.cost > 50000
name
Kris Windorski
Al Pacino
Al Pacino
Paris Hilton
Jessica Simpson
A.J. Hawk
car
Cavalier
Ferrari
Lexus
null
null
null
47
Car_Dealer
Sales
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id
where
s.cost > 50000
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
A left outer join returns data from the table on
the left regardless if it has a match in the table
on the right.
In this query in order for there to be a match
between the two tables the join criteria must
be met, which includes the following :
- Matching car dealer ids
Note: the “where” clause also contains
join/filter criteria, but logically it is
applied after the join/filter criteria in the
“from” clause have been applied
48
The following is an excerpt from the SQL Server 2005 Books Online
When SQL Server processes joins, the query engine chooses the most efficient
method (out of several possibilities) of processing the join. Although the physical
execution of various joins uses many different optimizations, the logical sequence
is:
1. The join conditions in the FROM clause are applied.
2. The join conditions and search conditions from the WHERE clause are
applied.
3. The search conditions from the HAVING clause are applied.
This sequence can sometimes influence the results of the query if conditions are
moved between the FROM and WHERE clauses
49
Car_Dealer
Sales
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
First, the join/filter conditions in the “from” clause are applied
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id
where
s.cost > 50000
car_dealer_id
1
1
2
2
3
4
4
5
name
sale_id
car
cost car_dealer_id
Kris Windorski
1
Porsche 40000
1
Kris Windorski
2
Cavalier 60000
1
Al Pacino
6
Ferrari 65000
2
Al Pacino
7
Lexus 61000
2
Paris Hilton
3
Mustang 30000
3
Jessica Simpson
4
Vibe 19000
4
Jessica Simpson
5
BMW 42000
4
A.J. Hawk
null
null
null
null
note: the query will only return name and
car, and not all columns
50
Car_Dealer
Sales
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
Next, the join/filter conditions in the “where” clause are applied
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id
where
s.cost > 50000
car_dealer_id
1
1
2
2
3
4
4
5
name
sale_id
car
cost car_dealer_id
Kris Windorski
1
Porsche 40000
1
Kris Windorski
2
Cavalier 60000
1
Al Pacino
6
Ferrari 65000
2
Al Pacino
7
Lexus 61000
2
Paris Hilton
3
Mustang 30000
3
Jessica Simpson
4
Vibe 19000
4
Jessica Simpson
5
BMW 42000
4
A.J. Hawk
null
null
null
null
becomes
name
Kris Windorski
Al Pacino
Al Pacino
car
Cavalier
Ferrari
Lexus
51