SQL: Querying Multiple Tables

Now It’s Relational!
SELECT c.CD_NAME, p.PREF_NAME, c.IN_STOCK
FROM CD_INVENTORY c, PERFORMERS p
WHERE c.PREF_ID = p.PREF_ID
AND c.IN_STOCK < 15;

More than 2 Tables!
SELECT c.CD_NAME, p.PREF_NAME, t.TYPE_NAME
FROM CD_INVENTORY c, PERFORMERS p,
PREF_TYPE t
WHERE c.PREF_ID = p.PREF_ID
AND p.TYPE_ID = t.TYPE_ID
AND TYPE_NAME = ‘Popular’;

Another Way to Join
SELECT c.CD_NAME, p.PREF_NAME, c.IN_STOCK
FROM CD_INVENTORY JOIN PERFORMERS p ON
c.PREF_ID = p.PREF_ID
WHERE c.IN_STOCK < 15;

LEFT OUTER JOIN
● Used to return all the rows from the LEFT
table, even if they don’t match
SELECT i.TITLE, t.TYPE_NAME, i.STOCK
FROM CD_INFO i LEFT OUTER JOIN CD_TYPE i
ON i.TYPE_ID = t.TYPE_ID;

RIGHT OUTER JOIN
Used to return all the rows from the RIGHT
table, even if they don’t match
FROM CD_INFO i RIGHT OUTER JOIN CD_TYPE i

FULL OUTER JOIN
Used to return all the UNMATCHED rows from
both tables
FROM CD_INFO i FULL OUTER JOIN CD_TYPE i

Combine Query Results
SELECT *
FROM CDS_CONTINUED
UNION
SELECT *
FROM CDS_DISCONTINUED;
● Must have same columns and data types!

SQL: Querying Multiple Tables

More Related Content

Viewers also liked

Similar to SQL: Querying Multiple Tables

More from RJ Podeschi

Recently uploaded

SQL: Querying Multiple Tables