A 38-Year-Old's MYSQL Primer 07 — Working with Multiple Tables

A 38-year-old's MYSQL primer 07 - working with multiple tables

I thought primer 06 was done but ended up reinforcing it and coming back to primer 07... 

Alright, shall we start slicing~

This round we'll practice querying across multiple tables instead of one.

(Heads-up: I use the same practice tables, but my examples won't match the book's one-to-one. ><) 

1. Set operations  

1) Sets and elements in SQL — remember Venn diagrams made of ellipses?  set : element = circle : contained value = table : row — that analogy should help. 

2) UNION — running a UNION drops the overlapping parts, as in the result below. 

select * from sample71_a;

select * from sample71_b;

select * from sample71_a union select * from sample71_b;

3) UNION + ORDER BY

When sorting the UNION result with order by, you can't apply it to just one side's SELECT. Instead you sort the UNION result via an alias.

select a c from sample71_a 
union 
select b c from sample71_b
order by c;

4) When you must combine without removing duplicates. 

select a c from sample71_a 
union all
select b c from sample71_b
order by c;

select * from 
  sample71_a , sample71_b 
where 
  sample71_a.a = sample71_b.b;

(2) Difference - outer-join the two tables and filter for rows lacking table B to emulate set difference. Outer join isn't something we've covered yet... but detailed exercises come up later. For now, just get a feel for how it works.

i) sample71_a - sample71_b

select 
  a
from 
  sample71_a a left outer join sample71_b b 
on 
  a.a = b.b 
where 
  b.b is null;

ii) sample71_b - sample71_a

select 
  b
from 
  sample71_a a right outer join sample71_b b 
on 
  a.a = b.b 
where 
  a.a is null;

2. Joining tables 

1) CROSS JOIN yields a cartesian product

Difference vs UNION: UNION adds vertically (rows), whereas combining in FROM via a product widens horizontally (columns).

select * from sample72_x, sample72_y;

2) Inner join via comma-separated tables in FROM

In real systems there can be lots of data tables. Cross-joining them would make combinations explode exponentially — unusable. That's why in practice inner joins are used most often.

Also, relational databases encourage each table's rows to have a unique value (a primary key). That's so other tables can reference those rows via a column of the same type and name as the primary key.

* Let's build a table including the names of groceries with stock counts of 200 and 500 ;D

* FYI the table names below are all in Korean — you wouldn't usually name tables in Korean. They're just for readability, so take them as pseudo-names.

(1) First, let's look at the full tables — cross-joining product and stock tables

select * from 상품, 재고수; 

(2) Cross join shows all duplicates too, so we need to tidy up. Both tables share a product_code column. Let's list them using that common key.

select * from 
  상품, 재고수 
where 
  상품.상품코드 = 재고수.상품코드;

(3) Now the data is much tidier. Let's narrow down to just the grocery category.

select 
  상품.상품명, 재고수.재고수 
from 
  상품, 재고수 
where 
  상품.상품코드 = 재고수.상품코드 
and 
  상품.상품분류='식료품';

3) inner join - inner joining with explicit syntax

Using the 상품 (products) and 재고수 (stock) tables -> let's compute the names and stock counts for items in the grocery category.

select 
  상품.상품명, 재고수.재고수 
from 
  상품 
inner join 
  재고수 
on 
  상품.상품코드 = 재고수.상품코드
where
 상품.상품분류='식료품';

4) Data management with an inner join 

Using 상품2 (products2) and 메이커 (maker) tables -> let's compute names and counts for grocery items.

select * from 상품2, 메이커; 

select * from 상품2 s, 메이커 m where s.메이커코드=m.메이커코드;

select 
  s.상품명, m.메이커명
from 
  상품2 s
inner join 
  메이커 M
on 
  s.메이커코드=m.메이커코드;

5) Outer join 

Inner join doesn't produce rows where one side's column values are missing. If you want to include such rows in the result, use an outer join. Technically we already did outer joins in the difference example above. Let's revisit it one more time.

The exercise: using 상품3 (products3) and 재고수 (stock), compute stock counts per grocery item name.

1) Inner join

select 
  s.상품명, m.재고수
from 
  상품3 s
inner join 
  재고수 M
on 
  s.상품코드=m.상품코드
where 
  s.상품분류='식료품';

2) Outer join 

Since I wrote the anchor product table on the left of JOIN, I declared it as LEFT JOIN.

Same pattern as the earlier difference:  (1) for A-B, use LEFT JOIN, (2) for B-A, use RIGHT JOIN — that's the pattern.

select 
  s.상품명, m.재고수
from 
  상품3 s
left join 
  재고수 M
on 
  s.상품코드=m.상품코드
where 
  s.상품분류='식료품';

+ Reference:  A site that looks useful, though I haven't tried it yet