sql多表查询

 

1.join 

SELECT *
FROM orders
JOIN customers
  ON orders.customer_id = customers.customer_id;

1. The first line selects all columns from our combined table. If we only want to select certain columns, we can specify which ones we want.
2. The second line specifies the first table that we want to look in, orders
3. The third line uses JOIN to say that we want to combine information from orders with customers.
4. The fourth line tells us how to combine the two tables. We want to match orders table’s cxustomer_id column with customers table’s customer_id column.

2.Inner Joins

SELECT COUNT(*)
FROM newspaper;

SELECT COUNT(*)
FROM online;

SELECT COUNT(*)
FROM newspaper
JOIN online
	ON newspaper.id = online.id;

3.Left Joins

SELECT *
FROM table1
LEFT JOIN table2
  ON table1.c2 = table2.c2;

1. The first line selects all columns from both tables.
2. The second line selects table1 (the “left” table).
3. The third line performs a LEFT JOIN on table2 (the “right” table).
4. The fourth line tells SQL how to perform the join (by looking for matching values in column c2).

eg:Suppose we want to know how many users subscribe to the print newspaper, but not to the online. 找到订阅了纸质报纸但是没有定网上报纸的人

首先分析，我们有一个online表和print表，如果一个人订阅了两种报纸，他在两个表里的id是相同的，我们要找订阅了纸质报纸但是没有定网上报纸的人，也就是要找把两个表连接之后，online那一栏为空的人，所以第一步我们先用id将两个表连起来。

同时对于那些只定了一种的人，我们需要找到他们，所以不希望在连接表的时候把这些人舍弃，所以我们要用左连接。

连接之后我们要找到没有定晚上报纸的人，所以我们要加一行where语句，把这些人筛选出来。

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

4.cross join

 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;

eg：现有一表，记录了n个人订阅报纸的起止时间，（假如开始订报的时间是1月，结束订报的时间是5月，那么表中数据为   name   1   5 )    要求计算每个月有多少人订报。

1.我们希望得到每个月订阅的人数，就需要知道所有人在一月，二月，三月...的订阅情况（是或否），然后把一月，二月，三月...的订阅情况汇总，就可以知道一年12个月，每个月的订阅情况，所以任务变成得到某一个月所有人的订阅情况

2.如果要知道所有人在某个月订阅情况，我们就需要知道一个人在这个月的订阅情况，然后再去找下一个人的订阅情况，所以任务变成了得到某一个人在某一个月有没有订阅

3.作为人，我们可以通过判断某个月份在不在起止日期内得到这个人在这个月有没有订报纸，但是sql不能这么判断，所以我们的任务变成了 如何让sql通过起止日期，判断一个人在给定月份是否订阅

4.对于题目中的例子来说，这个表通过起始日期的方式，将一个人订报的5条记录（每个月一条）合并成了1条记录，我们可以将这一条数据拆分成5条

既  ：（name   1）表示这个人1月订阅了报纸

如果我们得到这样的数据，我们就可以知道某个人是否在给定月份订阅了报纸

所以我们的任务变成了将原始数据转变为（name   1）这样的数据

5.创建一个月份表，表中只有1到12的数字，代表12个月，然后cross join这个表和报纸表连接，这样每1条记录就都被扩充为了12条记录

SELECT *
FROM newspaper
CROSS JOIN months;

 6.然后选择  start_month <= month 和 end_month >= month的数据，就达到了刚刚所说的“将起始日期转变为（name   1）这样的数据”

SELECT *
FROM newspaper
CROSS JOIN months
WHERE start_month <= month 
  AND end_month >= month;

 7.最后group by month，再count（*）即可

SELECT month, 
  COUNT(*)
FROM newspaper
CROSS JOIN months
WHERE start_month <= month 
  AND end_month >= month
GROUP BY month;

 5.Union

Suppose we have two tables and they have the same columns.

SELECT *
FROM table1
UNION
SELECT *
FROM table2;

The result would be:

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.

select <列名1><列名2> from table, union all select <列名1><列名2> from table2，会保留两个表中的数据

6.with

有时候我们希望将一个结果作为内容连接到另一个表里，这时候我们可以用 with 暂时性地床在一个新表来存放那个结果，然后合并两个表

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;

 7. review

• JOIN will combine rows from different tables if the join condition is true.

• LEFT JOIN will 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 is a column that serves a unique identifier for the rows in the table.

• Foreign key is a column that contains the primary key to another table.

• CROSS JOIN lets us combine all rows of one table with all rows of another table.

• UNION stacks one dataset on top of another.

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