Multiple Tables

SQL에서 여러 Table을 다루는 방법을 정리하였습니다.

 

 

---

< Introduction >

In order to efficiently store data, we often spread related information across multiple tables.

 

---

Example : imagine that we’re running a magazine company where users can have different types of subscriptions to different products.

 

< We could have one table with all of the following information : >

• order_id
• customer_id
• customer_name
• customer_address
• subscription_id
• subscription_description
• subscription_monthly_price
• subscription_length
• purchase_date

However, a lot of this information would be repeated. If the same customer has multiple subscriptions, that customer’s name and address will be reported multiple times. If the same subscription type is ordered by multiple customers, then the subscription price and subscription description will be repeated.
This will make our table big and unmanageable.

< So instead, we can split our data into three tables : >

 

1. orders would contain just the information necessary to describe what was ordered:

• order_id, customer_id, subscription_id, purchase_date

2. subscriptions would contain the information to describe each type of subscription:

• subscription_id, description, price_per_month, subscription_length

3. customers would contain the information for each customer:

• customer_id, customer_name, address

 

 

---

< Combining Tables Manually >

Doing this kind of matching below is called joining two tables.

 

---

Example : Suppose we have the three tables described in the previous exercise

 

1. orders : a table with information on each magazine purchase

order_id	customer_id	subscription_id	purchase
1	2	3	2017-01-01
2	2	2	2017-01-01
3	2	1	2017-01-01

2. subscriptions: a table that describes each type of subscription

subscription_id	description	price_per_month	length
1	Politics Magazine	5	12 months
2	Fashion  Magazine	10	6 months
3	Sports Magazine	7	3 months

3. customers: a table with customer names and contact information

customer_id	customer_name	address
1	John Smith	123 Main St
2	Jane Doe	456 Park Ave
3	Joe Schmo	798 Broadway

 

=> If we just look at the orders table, we can’t really tell what’s happened in each order. However, if we refer to the other tables, we can get a complete picture.

For Example, to find out the customer’s name, we look at the customers table and look for the item with a customer_id value of 2. We can see that Customer 2’s name is ‘Jane Doe’ and that she lives at ‘456 Park Ave’.

 

 

---

< Combining Tables with SQL >

Combining tables manually is time-consuming. So, SQL gives us an easy sequence for this: JOIN.

 

< Syntax >

SELECT *
FROM table_name1
JOIN table_name2
  ON __________;

- JOIN to say that we want to combine information from table_name1 with table_name1.
- The fourth line (ON condition) tells how to combine the two tables.

 

Example : Join orders table and subscriptions table and select all columns.

SELECT *
FROM orders
JOIN subscriptions
  ON orders.subscription_id = subscriptions.subscription_id;

order_id	customer_id	subscription_id	purchase _date	subscription_id	description	price_per _month	subscription_length
1	3	2	01-10-2017	2	Politics Magazine	11	6 months
2	2	4	01-9-2017	4	Fashion Magazine	15	12 months
3	3	4	01-26-2017	4	Fashion Magazine	15	12 months
4	9	9	01-4-2017	9	Sports Magazine	13	3 months
5	7	5	01-25-2017	5	Fashion Magazine	17	6 months

Note : Because column names are often repeated across multiple tables,

we use the syntax table_name.column_name to be sure that our requests for columns are unambiguous.

 

 

---

< Inner Joins >

When we perform a simple JOIN (often called an inner join), our result only includes rows that match our ON condition.

 

&amp;amp;amp;lt; INNER JOIN &amp;amp;amp;gt;

-> The first and last rows have matching values of c2. The middle rows do not match. The final result has all values from the first and last rows but does not include the non-matching middle row.

 

 

---

< Left Joins >

When perform a JOIN, to combine two tables and keep some of the un-matched rows

 

< Syntax >

SELECT *
FROM table_name1
LEFT JOIN table_name2
  ON __________;

 

&amp;amp;amp;lt; Left Join &amp;amp;amp;gt;

-> The first and last rows have matching values of c2. The middle rows do not match. The final result will keep all rows of the first table but will omit the un-matched row from the second table.

 

Example : want to know how many users subscribe to the print newspaper, but not to the online.

SELECT *
FROM newspaper
LEFT JOIN online
  ON newspaper.id = online.id
WHERE online.id IS NULL;

&amp;amp;amp;lt; Query Results &amp;amp;amp;gt;

 

 

---

< Primary Key vs Foreign Key >

The most common types of joins will be joining a foreign key from one table with the primary key from another table.

 

Example) : orders table

order_id	customer_id	subscription_id	purchase
1	2	3	2017-01-01
2	2	2	2017-01-01
3	2	1	2017-01-01

: When the primary key for one table appears in a different table, it is called a foreign key.

So customer_id is a primary key when it appears in customers, but a foreign key when it appears in orders.

 

 

---

< Cross Join >

So far, we’ve focused on matching rows that have some information in common.
But sometimes, we just want to combine all rows of one table with all rows of another table.

 

< Syntax >

SELECT *
FROM table_name1
CROSS JOIN table_name2

- CROSS JOIN don’t require an ON statement.

 

Example : if we had a table of shirts and a table of pants, we might want to know all the possible combinations to create different outfits.

SELECT shirts.shirt_color,
   pants.pants_color
FROM shirts
CROSS JOIN pants;

shirt_color	pants_color
white	light denim
white	black
grey	light denim
grey	black
olive	light denim
olive	black

 

 

---

< Union >

To stack one dataset on top of the other.

 

< Syntax >

SELECT *
FROM table_name1
UNION
SELECT *
FROM table_name2;

- SQL has strict rules for appending data :

• Tables must have the same number of columns.
• The columns must have the same data types in the same order as the first table.

 

Example)

table1 :

pokemon	type
Bulbasaur	Grass
Charmander	Fire
Squirtle	Water

table2 :

pokemon	type
Snorlax	Normal

---

SELECT *
FROM table1
UNION
SELECT *
FROM table2;

pokemon	type
Bulbasaur	Grass
Charmander	Fire
Squirtle	Water
Snorlax	Normal

 

 

---

< With >

To combine two tables, but one of the tables is the result of another calculation.

WITH allows us to define one or more temporary tables that can be used in the final query.

 

< Syntax >

WITH previous_results AS (
   SELECT ...
   ...
   ...
   ...
)
SELECT *
FROM previous_results;

Essentially, put a whole first query inside the parentheses ( ) and giving it a name(previous_results).
After that, we can use this name as if it’s a table and write a new query using the first query.

 

Example : Our marketing department want to know how many magazines each customer subscribes to.

Not the customer's id, But the customer's name

WITH previous_query AS (
  SELECT customer_id,
    COUNT(subscription_id) AS 'subscriptions'
  FROM orders
  GROUP BY customer_id
)
SELECT customers.customer_name, previous_query.subscriptions
FROM previous_query
JOIN customers
  ON previous_query.customer_id = customers.customer_id;

customer_name	subscriptions
Allie Rahaim	4
Jacquline Diddle	1
Lizabeth Letsche	6
Jessia Butman	2
Inocencia Goyco	2
Bethann Schraub	1
Janay Priolo	1
Ophelia Sturdnant	1
Eryn Vilar	2

 

 

Reference : SQL Subquery

Writing Subqueries in SQL | Advanced SQL - Mode

 

Reference : SQL ZOO

SQLZOO