下面的查詢基于的表有：

•  "Websites" 表：

•  access_log表：

• students表：

1、多表查詢

1.1、基本查詢（笛卡爾積）

SELECT查詢不但可以從一張表查詢數據，還可以從多張表同時查詢數據。

查詢多張表的語法：

select * from 表1, 表2;

# 示例：
SELECT * FROM websites, access_log;

查詢結果類似：

上面查詢出來的將是 access_log 和 websites 表的笛卡爾積，也就是 access_log 表的每個記錄分別跟 websites 表的所有記錄的組合。 結果集的列數是 access_log 表和 websites 表的列數之和，行數是 access_log 表和 websites 表行數之積。比如 a 表有 2 條數據，每條數據有 3 個字段，b 表有 4 條數據，每條數據有 5 個字段，則笛卡爾積的結果將有 2*4 = 8 條數據，并且每條數據將有 3+5=8 個字段。

在多表查詢時，不同表之間可能有相同的列名稱，此時我們應該通過 “表名.列名” 的方式來區別不同表之間的列，或者也可以直接給表起別名，通過 “表別名.列名” 的方式來區分：

SELECT
    w.id,
    w.NAME,
    a.aid,
    a.site_id 
FROM
    websites w,
    access_log a;

查詢結果類似：

JOIN的含義就如英文單詞“join”一樣，連接兩張表，大致分為內連接，外連接，右連接，左連接，自然連接。

1.2、內連接（交集、INNER JOIN或JOIN）

內連接INNER JOIN是最常用的連接操作，從數學的角度講就是求兩個表的交集。實際上就是在兩個表的笛卡爾積中，通過 on 條件過濾結果。

select 字段列表 from 表1 [inner] join 表2 on 條件   -- inner可省略不寫

示例：

SELECT a.id, a.name, b.aid, b.date
FROM websites a
INNER JOIN access_log b ON a.id = b.site_id;

-- 內連接語法可以用where替代，等價于上面語句，可以稱為隱式內連接
SELECT a.id, a.name, b.aid, b.date
FROM websites a, access_log b 
WHERE a.id = b.site_id;

查詢結果：

1.2.1、內連接的 on 和 where 條件是等價的

實際上 inner join 中的 on 的條件都可以寫在 where 里，兩者的結果是一樣的。這是因為使用 inner join 時需兩表條件同時滿足才能查詢出來，跟寫在 where 里效果是一樣的。但需注意，left join 這么寫兩者的結果是不等價的。

-- inner join 條件都寫在 on 后面
select
    a.id,
    a.name,
    b.aid,
    b.date,
    b.count
from
    websites a
inner join access_log b on
    a.id = b.site_id 
    and b.count > 40
order by a.id asc;
    
-- 等價于inner join 條件部分寫在on后，部分寫在where里    
select
    a.id,
    a.name,
    b.aid,
    b.date,
    b.count
from
    websites a
inner join 
    access_log b on
    a.id = b.site_id
where
    b.count > 40
order by a.id asc

-- 也等價于inner join 條件全部寫在where里
select
    a.id,
    a.name,
    b.aid,
    b.date,
    b.count
from
    websites a
inner join 
    access_log b
where
    a.id = b.site_id
    and b.count > 40
order by a.id asc

以上三種寫法查詢結果均如下：

1.3、左連接（LEFT JOIN）

1.3.1、左外連接（常用）

左外連接LEFT JOIN的含義就是求兩個表的交集外加左表剩下的數據。實際上就是從兩個表的笛卡爾積中，先通過 on 條件過濾結果，然后再加上左表中所有剩余的記錄即可。（也就是說，最終會將左表中不符合 on 條件的記錄也一并加上，實際上也就是左表中的所有記錄都會存在于最終結果中）

select 字段列表 
from 左表 left join 右表 
on 條件;

示例，下面以 access_log 為左表，websites 為右表：

SELECT * 
FROM access_log LEFT JOIN websites 
ON access_log.aid=websites.id;

查詢結果：

1.3.2、左外連接的 on 和 where 條件非等價

在 left join中，條件寫在 on 里和寫在 where 里結果是不一樣的。on 條件是先對表做過濾再關聯，而 where 是先關聯查詢結果后再過濾，當使用左或右關聯時，兩者的結果是不一樣的，這跟 inner join的表現不同。

示例如下：

-- 語句1
select
    *
from
    access_log lg
left join websites web
    on lg.aid = web.id
    and web.country = 'CN'
order by
    lg.aid asc

-- 并不等價于以下語句2
select
    *
from
    access_log lg
left join websites web
    on lg.aid = web.id
where web.country = 'CN'
order by
    lg.aid asc

• 語句1結果如下，可以看到是先過濾再關聯，左表所有記錄都會保留

• 語句2結果如下，可以看到是先得到關聯的查詢結果后再做整體過濾，數據量少很多

1.3.3、左外連接可轉inner join（當where對右表做過濾時）

使用左外連接時，當where條件里對被關聯表（右表）做了過濾，left join 有可能可被等價轉換轉成 inner join。比如對右表的某列做 != 'xx' 或 = 'xx' 的過濾條件，保證了右表記錄非NULL，此時就可以用 inner join代替。

select
    *
from
    access_log lg
left join websites web
    on lg.aid = web.id
where web.country != 'CN'
order by
    lg.aid asc

-- 等價于直接用 inner join
select
    *
from
    access_log lg
inner join websites web
    on lg.aid = web.id and web.country != 'CN'
order by
    lg.aid asc    

注意，必須是在 where 條件里對右表做過濾時才可轉為 inner join，如果是對左表做過濾則不能轉換。

1.3.4、左連接（A LEFT JOIN B ON A.KEY = B.KEY WHERE B.KEY IS NULL）

左連接LEFT JOIN 是左外連接中，只保留那些不屬于兩個表的交集的數據。實際上就是從兩個表的笛卡爾積中，先通過 on 條件過濾結果，然后再加上左表中所有剩余的記錄，然后再去掉兩表中交集的部分。

也就是相當于是上面的左外查詢中只保留紅框框起來的數據：

1.4、右連接（RIGHT JOIN）

1.4.1、右外連接（常用）

同理，右連接RIGHT JOIN就是求兩個表的交集外加右表剩下的數據。實際上就是從兩個表的笛卡爾積中，先通過 on 條件過濾結果，然后再加上右表中所有剩余的記錄即可。（也就是說，最終會將右表中不符合 on 條件的記錄也一并加上，實際上也就是右表中的所有記錄都會存在于最終結果中）

select 字段列表
from 左表 right join 右表
on 條件

示例，下面以 websites 為左表，access_log 為右表：

SELECT * 
FROM websites 
RIGHT JOIN access_log 
ON access_log.aid=websites.id;

查詢結果：

1.4.2、右連接（A RIGHT JOIN B ON A.KEY = B.KEY WHERE A.KEY IS NULL）

右連接RIGHT JOIN 是右外連接中，只保留那些不屬于兩個表的交集的數據。實際上就是從兩個表的笛卡爾積中，先通過 on 條件過濾結果，然后再加上右表中所有剩余的記錄，然后再去掉兩表中交集的部分。

也就是上面的左外查詢中只保留紅框框起來的數據：

1.5、全外連接

全外連接：左表和右表都不做限制，所有的記錄都顯示，兩表不足的地方用null 填充。全外連接= 左表全部記錄+右表全部記錄+相關聯結果 = 左外連接+右外連接-相關聯結果（即去除重復）

（請注意，圖中 full outer join 語法在 mysql 中是不支持使用的）

在 mysql 里全外連接的sql語句就是 union 左外連接和右外連接的結果。請注意不是 union all，而是使用了 union，union 會自動去除重復結果。

SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.id)
UNION 
SELECT * FROM table1 RIGHT JOIN table2 ON (table1.id = table2.id)

示例：

SELECT * FROM access_log LEFT JOIN websites ON access_log.aid=websites.id
UNION 
SELECT * FROM access_log RIGHT JOIN websites ON access_log.aid=websites.id;

查詢結果如下：（下面我額外給websites表添加了一條 id為15的記錄，以便觀察右外連接的結果）

1.6、兩表獨有的數據集

（請注意，圖中 full outer join 語法在 mysql 中是不支持使用的）

在mysql中就是union了左連接和右連接

SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.id) WHERE table2.id IS NULL
UNION 
SELECT * FROM table1 RIGHT JOIN table2 ON (table1.id = table2.id) WHERE table1.id IS NULL

示例：

SELECT * FROM access_log LEFT JOIN websites ON access_log.aid=websites.id where websites.id is null
UNION 
SELECT * FROM access_log RIGHT JOIN websites ON access_log.aid=websites.id where access_log.aid is null;

查詢結果如下：（下面額外給websites表添加了一條 id為15的記錄，以便觀察右連接的結果）

2、子查詢

子查詢允許把一個查詢嵌套在另一個查詢當中。

2.1、where型子查詢 

where 型子查詢把內層查詢結果當作外層查詢的比較條件。

如果內層查詢返回一個標量值（就一個值），那么外部查詢就可以使用比較符，比如：=、>、<、>=、<=和<>符號進行比較判斷。

比如下面查詢 count 值最大的相關信息：

SELECT * 
FROM access_log 
WHERE count = (SELECT MAX(access_log.count) FROM access_log);

查詢結果：

如果子查詢返回的不是一個標量值，而是一個N行單列的結果集，但外部查詢使用了比較符和子查詢的結果集進行比較，此時會拋出異常。

如果子查詢返回的不是一個標量值，而是一個N行單列的結果集，此時可以使用 IN、ANY、SOME 和 ALL 操作符，不能直接使用 = > < >= <= <> 這些比較標量結果的操作符。

SELECT * 
FROM websites 
WHERE id IN (SELECT id from websites WHERE country = 'CN');

2.2、from型子查詢

from 型子查詢把內層的查詢結果當成臨時表，供外層sql再次查詢。當子查詢返回的結果是多行多列時，子查詢的結果集可以當成表看待，一般要給這個臨時表起一個別名，否則臨時表沒有名稱則無法訪問臨時表中的字段。

SELECT * 
FROM (SELECT id,`name`,alexa FROM websites WHERE country = 'CN') temp
WHERE temp.alexa > 15;