SQL
Multiple-Table Queries
Objectives
Be able to retrieve data from multiple
tables
Be able to use an alias
Be able to join a table to itself
Querying Multiple Tables
Join
Indicate in the SELECT clause all the columns
to display.
Indicate in the FROM clause all tables
involved in the query.
Give Condition(s) in the WHERE clause to
restrict the data to be retrieved to only those
rows that have common values in matching
columns.
Retrieving Data from
Multiple Tables
Tables may be joined when a column from one
table is the same length and type as a column in
another table.
Typically, the primary key in one table is
matched to the foreign key in another table.
The Where clause is used to join the two
columns together.
Often the column names must be qualified:
Tablename.columnname
Join
Join creates a new table (virtual table) from two existing
tables by matching on a column common to both tables
Equijoin
The new table contains two identical columns
SELECT * FROM STOCK, NATION
WHERE STOCK.NATCODE = NATION.NATCODE
Natural join
One of the identical columns is dropped from an equijoin
SELECT STKCODE, STKFIRM, STKPRICE, STKQTY,
STKDIV, STKPE, STOCK.NATCODE, EXCHRATE, NATNAME
FROM STOCK, NATION
WHERE STOCK.NATCODE = NATION.NATCODE
Join
SELECT * FROM STOCK, NATION
WHERE STOCK.NATCODE = NATION.NATCODE
STKCODE STKFIRM STKPRICE STKQTY STKDIV STKPE NATCODE NATCODE1 NATNAME EXCHRATE
NE Narembeen Emu 12.34 45619 1.00 8 AUS AUS Australia 0.46000
IR Indooroopilly Ruby 15.92 56147 0.50 20 AUS AUS Australia 0.46000
QD Queensland Diamond 6.73 89251 0.50 7 AUS AUS Australia 0.46000
BD Bombay Duck 25.55 167382 1.00 12 IND IND India 0.02280
ROF Royal Ostrich Farms 33.75 1234923 3.00 6 UK UK United Kingdom 1.00000
CS Canadian Sugar 52.78 4716 2.50 15 UK UK United Kingdom 1.00000
FC Freedonia Copper 27.50 10529 1.84 16 UK UK United Kingdom 1.00000
BS Bolivian Sheep 12.75 231678 1.78 11 UK UK United Kingdom 1.00000
BE Burmese Elephant 0.07 154713 0.01 3 UK UK United Kingdom 1.00000
ILZ Indian Lead & Zinc 37.75 6390 3.00 12 UK UK United Kingdom 1.00000
SLG Sri Lankan Gold 50.37 32868 2.68 16 UK UK United Kingdom 1.00000
AR Abyssinian Ruby 31.82 22010 1.32 13 UK UK United Kingdom 1.00000
PT Patagonian Tea 55.25 12635 2.50 10 UK UK United Kingdom 1.00000
NG Nigerian Geese 35.00 12323 1.68 10 UK UK United Kingdom 1.00000
MG Minnesota Gold 53.87 816122 1.00 25 US US United States 0.67000
GP Georgia Peach 2.35 387333 0.20 5 US US United States 0.67000
Join
Report the value of each stock holding in UK pounds. Sort the
report by nation and firm.
SELECT NATNAME, STKFIRM, STKPRICE, STKQTY, EXCHRATE,
STKPRICE*STKQTY*EXCHRATE
FROM STOCK, NATION
WHERE STOCK.NATCODE = NATION.NATCODE
ORDER BY NATNAME, STKFIRM
NATNAME STKFIRM STKPRICE STKQTY EXCHRATE EXPRESSION 1
Australia Indooroopilly Ruby 15.92 56147 0.46000 411175.71
Australia Narembeen Emu 12.34 45619 0.46000 258951.69
Australia Queensland Diamond 6.73 89251 0.46000 276303.25
India Bombay Duck 25.55 167382 0.02280 97506.71
United Kingdom Abyssinian Ruby 31.82 22010 1.00000 700358.20
United Kingdom Bolivian Sheep 12.75 231678 1.00000 2953894.50
United Kingdom Burmese Elephant 0.07 154713 1.00000 10829.91
United Kingdom Canadian Sugar 52.78 4716 1.00000 248910.48
United Kingdom Freedonia Copper 27.50 10529 1.00000 289547.50
United Kingdom Indian Lead & Zinc 37.75 6390 1.00000 241222.50
United Kingdom Nigerian Geese 35.00 12323 1.00000 431305.00
United Kingdom Patagonian Tea 55.25 12635 1.00000 698083.75
United Kingdom Royal Ostrich Farms 33.75 1234923 1.00000 41678651.25
United Kingdom Sri Lankan Gold 50.37 32868 1.00000 1655561.16
United States Georgia Peach 2.35 387333 0.67000 609855.81
United States Minnesota Gold 53.87 816122 0.67000 29456209.73
Joining Database Tables
Examples:
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE,
VENDOR.V_NAME, VENDOR.V_CONTACT,
VENDOR.V_AREACODE, VENDOR.V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
SELECT P_DESCRIPT, P_PRICE, V_NAME,
V_CONTACT, V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE
ORDER BY P_PRICE;
Examples
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE
AND P_INDATE > ‘12-NOV-99’;
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
V_AREACODE, V_PHONE
FROM PRODUCT, VENDOR
WHERE PRODUCT.V_CODE = VENDOR.V_CODE
ORDER BY P_PRICE;
In
Joining tables on matching columns produces
the same results as using a subquery with In.
Select partdesc from part
where partno in (Select partno
from orderline where ordno = ‘12491’);
OR
Select partdesc from part, orderline
where part.partno = orderline.partno
and ordno = ‘12491’;
GROUP BY
- reporting by groups
Report the total value of stockholdings by nation.
SELECT NATNAME, SUM(STKPRICE*STKQTY*EXCHRATE)
FROM STOCK, NATION WHERE STOCK.NATCODE = NATION.NATCODE
GROUP BY NATNAME
NATNAME SUM(EXPRESSION 1)
Australia 946430.65
India 97506.71
United Kingdom 48908364.25
United States 30066065.54
HAVING
- the WHERE clause of
groups
Report the total value of stockholdings by nation for nations
with two or more listed stocks.
SELECT NATNAME, SUM(STKPRICE*STKQTY*EXCHRATE)
FROM STOCK, NATION WHERE STOCK.NATCODE = NATION.NATCODE
GROUP BY NATNAME
HAVING COUNT(*) => 2
NATNAME SUM(EXPRESSION 1)
Australia 946430.65
United Kingdom 48908364.25
United States 30066065.54
Exists
Exists will have a value of true if the subquery
returns one or more rows and false if no rows
are returned.
What are thet order#s for all parts finished in
red oak?
Select distinct ordno from orderline
where exists
(Select * from part
where partno = orderline.partno
and partfinish = ‘Red Oak’);
Correlated Subquery
Usually Exists is used in a correlated
subquery.
A correlated subquery is when the inner
query refers to a table used in the outer
query (as the previous example did).
The inner query is computed for each
outer row
Subquery within a
Subquery
A nested query may be used within a nested
query or multiple tables may be joined.
Find the order number & date for every order that
includes a part located in warehouse 3.
Select ordno, orddate from orders
where ordno in (Select ordno from orderline
where partno in (Select partno from part
where warehouseno = ‘3’));
Nested Subquery Evaluation
Step 1:
Innermost subquery is evaluated first, producing a
temporary table of part numbers located in
warehouse 3
Step 2:
Intermediate subquery is evaluated, producing a
second temporary table with a list of order numbers
Step 3:
Outer query is evaluated last, producing the desired
list of order numbers and order dates using only
those orders whose numbers are in the temporary
table produced in step 2
Joining 3 Tables
The previous query may also be written as:
Select orders.ordno, orddate
from orders, orderline, part
where orders.ordno = orderline.ordno and
orderline.partno = part.partno and
warehouseno = ‘3’;
If there are 3 tables, then 2 where clauses must
join matching columns.
An Alias
Tables listed in the FROM clause can be
given an alternative name
An alias is an alternative name for a table
that may be used when qualifying column
names in order to reduce your typing.
Select ol.ordno, p.partno
from orderline ol, part p
where ol.partno = p.partno;
Joining a table to itself
Find every pair of customers who have
the same first and last name.
Select c.custno, c.last, c.first, k.custno, k.last,
k.first
from customer c, customer k
where c.last = k.last and c.first = k.first
and c.custno Any (Select balance
from customer where salesrepno = ‘10’);