###### tags: `sql`
# Multiple tables(course4)
* JOIN : combine rows from different tables if the join condition is true
* LEFT JOIN：return every row in the left table, and if the join condition is not met, NULL values are used to fill in the columns from the right table
* Primary key & Foreign key
* CROSS JOIN：combine all rows of one table with all rows of another table
* UNION：stacks one dataset on top of another
* WITH：define one or more temporary tables that can be used in the final query
### 1. Combining tables : JOIN

SELECT *
FROM orders --pecifies the first table that we want to look in
JOIN customers --uses JOIN to say that we want to combine info from orders with customers
  ON orders.customer_id = customers.customer_id; --table_name.column_name
  --match orders table’s customer_id column with customers table’s customer_id column
  

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

Consider the following animation, which illustrates an inner join of two tables on table1.c2 = table2.c2 :

The first and last rows have matching values of c2. The middle rows do not match. 最後只會顯示第一跟第三列。

SELECT COUNT(*)
FROM newspaper;
SELECT COUNT(*)
FROM online;
--Join newspaper table and online table on their id columns (the unique ID of the subscriber)
SELECT COUNT(*)  --顯示有幾列
FROM newspaper
JOIN online
  ON newspaper.id = online.id; 

### 3. Left Joins
A *left join* will keep all rows from the first table, regardless of whether there is a matching row in the second table.

SELECT *
FROM table1
LEFT JOIN table2
  ON table1.c2 = table2.c2;
--
SELECT *
FROM newspaper
LEFT JOIN online
  ON newspaper.id = online.id
WHERE online.id IS NULL; --select rows where there was no corresponding row from the online table

### 4. Primary Key vs Foreign Key
ex:
現在有3個table，分別是orders, subscriptions, customers
Each of these tables has a column that uniquely identifies each row of that table:
* order_id for orders
* subscription_id for subscriptions
* customer_id for customers

Primary keys有三個限制:
* None of the values can be NULL.
* Each value must be unique.
* A table can not have more than one primary key column.

When the primary key for one table appears in a different table, it is called a **foreign key**.
ex : 下圖為orders表格，可以看出customer_id跟subscription_id出現在同一個table中
所以當customer_id出現在customers table，就是primary key；但如果出現在orders table，就是foreign key。
### 5. Cross Join
WHEN：combine all rows of one table with all rows of another table
#### ex 1 :

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

Notice that cross joins don’t require an **ON** statement. You’re not really joining on any columns!

假如有3種shirts(white, grey, olive)跟兩種pants(light denim, black)，結果如下：

| shirts color | pants color |
| ------------ | ----------- |
| white | light denim |
| white | black |
| grey | light denim |
| grey | black |
| olive | light denim |
| olive | black |

3 shirts × 2 pants = 6 combination

A more common usage of **CROSS JOIN** is when we need to compare each row of a table to a list of values.
#### ex 2 :
現有一table名為newspaper，其中兩行分別為
* start_month : the first month where the customer subscribed to the print newspaper (i.e., 2 for February)
* end_month : the final month where the customer subscribed to the print newspaper
我們想知道在當年度的每個月份訂閱人數:

--1 計算三月訂閱報紙人數
SELECT COUNT(*)
FROM newspaper
WHERE start_month <= 3 and end_month >= 3;
--2 在database中有另一個table months包含1~12月
select *
from newspaper
cross join months;
--3 增加兩個條件式
select *
from newspaper
cross join months
where start_month <= month and --This will select all months where a user was subscribed.
  end_month >= month;
--4 final query where aggregate over each month to count the number of subscribers
select month, 
  count(*)
from newspaper
cross join months
where start_month <= month and end_month >= month
group by month;


### 6. Union
WHEN：stack one dataset on top of the other

SELECT *
FROM table1
UNION
SELECT *
FROM table2;

table2的資料會疊加在table1下面
### 7. With
WHEN : combine two tables, but one of the tables is the result of another calculation

WITH previous_results AS ( --AS is how you give something an alias
   SELECT ...
   ...
   ...
   ...
) --put a whole first query inside the parentheses() and giving it a name
SELECT *
FROM previous_results
JOIN customers
  ON _____ = _____;
--- ex
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 customers.customer_id = previous_query.customer_id;