SQL: Multiple Tables CIT 381

1
SQL Multiple TablesCIT 381
2
Sample Schema
3
General Form
4
Naming Attributes
5
Renaming
6
FROM and Cross Product
SELECT customers.company, customers.cust_num,
orders.cust, orders.order_num FROM customers,
orders
FROM creates the cross product of two (or
more) tables. Here, every row of customers is
paired with every row of orders. Note this
particular example is from the order processing
database used in the SQL text. Since customers
has 21 rows and orders has 30, the result of the
above query returns 2130 630 rows.
7
(No Transcript)
8
Joins
What we want to do is compute joins, matching
the shared attribute in the two tables. stud_ssn
in student stud_ssn in enroll This is by far
the most important and common operation.
9
Computation of Joins
FROM student s, enroll e drives all pairs of
rows from the two tables into the
condition. WHERE s.stud_ssne.stud_ssn picks
out those pairs that match on the desired
attribute. This is sometimes called a natural
join.
10
Long Join Chains
Find the name of all classes taken by Alice. (We
must look in three tables.) SELECT c.class_dept,
c.class_num, c.class_title FROM student s,
enroll e, class c WHERE s.stud_nameAlice
AND s.stud_ssne.stud_ssn AND e.class_crnc.clas
s_crn
11
Joins in Access
SELECT CUSTOMERS.COMPANY, CUSTOMERS.CUST_NUM,
ORDERS.ORDER_NUM FROM CUSTOMERS INNER JOIN ORDERS
ON CUSTOMERS.CUST_NUM ORDERS.CUST
MS Access, while allowing the queries
on previous slides, also allows for a
non-standard syntax (Jet SQL). An inner join
(what weve seen), is to be distinguished from an
outer join, which we will see soon.