【SQL】13 SQL 別名、SQL 連線(JOIN)、SQL INNER JOIN 關鍵字、SQL LEFT JOIN 關鍵字、SQL RIGHT JOIN 關鍵字、SQL FULL OUTER J

HelloZEX發表於2018-07-26
SQL

通過使用 SQL，可以為表名稱或列名稱指定別名。

SQL 別名

通過使用 SQL，可以為表名稱或列名稱指定別名。

基本上，建立別名是為了讓列名稱的可讀性更強。

列的 SQL 別名語法

SELECT column_name AS alias_name
FROM table_name;

表的 SQL 別名語法

SELECT column_name(s)
FROM table_name AS alias_name;

 

演示資料庫

在本教程中，我們將使用 RUNOOB 樣本資料庫。

下面是選自 "Websites" 表的資料：

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘寶          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鳥教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

下面是 "access_log" 網站訪問記錄表的資料：

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

 

列的別名例項

下面的 SQL 語句指定了兩個別名，一個是 name 列的別名，一個是 country 列的別名。提示：如果列名稱包含空格，要求使用雙引號或方括號：

例項

SELECT name AS n, country AS c
FROM Websites;

執行輸出結果：

在下面的 SQL 語句中，我們把三個列（url、alexa 和 country）結合在一起，並建立一個名為 "site_info" 的別名：

例項

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

執行輸出結果：

 

表的別名例項

下面的 SQL 語句選取 "菜鳥教程" 的所訪問記錄。我們使用 "Websites" 和 "access_log" 表，並分別為它們指定表別名 "w" 和 "a"（通過使用別名讓 SQL 更簡短）：

例項

SELECT w.name, w.url, a.count, a.date 
FROM Websites AS w, access_log AS a 
WHERE a.site_id=w.id and w.name="菜鳥教程";

執行輸出結果：

不帶別名的相同的 SQL 語句：

例項

SELECT Websites.name, Websites.url, access_log.count, access_log.date 
FROM Websites, access_log 
WHERE Websites.id=access_log.site_id and Websites.name="菜鳥教程";

執行輸出結果：

在下面的情況下，使用別名很有用：

  1. 在查詢中涉及超過一個表

  2. 在查詢中使用了函式

  3. 列名稱很長或者可讀性差

  4. 需要把兩個列或者多個列結合在一起

SQL 連線(JOIN)

SQL join 用於把來自兩個或多個表的行結合起來。

SQL JOIN

SQL JOIN 子句用於把來自兩個或多個表的行結合起來，基於這些表之間的共同欄位。

最常見的 JOIN 型別：SQL INNER JOIN（簡單的 JOIN）。 SQL INNER JOIN 從多個表中返回滿足 JOIN 條件的所有行。

演示資料庫

在本教程中，我們將使用 RUNOOB 樣本資料庫。

下面是選自 "Websites" 表的資料：

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘寶          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鳥教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

下面是 "access_log" 網站訪問記錄表的資料：

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

請注意，"Websites" 表中的 "id" 列指向 "access_log" 表中的欄位 "site_id"。上面這兩個表是通過 "site_id" 列聯絡起來的。

然後，如果我們執行下面的 SQL 語句（包含 INNER JOIN）：

例項

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;

執行以上 SQL 輸出結果如下：

 

不同的 SQL JOIN

在我們繼續講解例項之前，我們先列出您可以使用的不同的 SQL JOIN 型別：

  • INNER JOIN：如果表中有至少一個匹配，則返回行
  • LEFT JOIN：即使右表中沒有匹配，也從左表返回所有的行
  • RIGHT JOIN：即使左表中沒有匹配，也從右表返回所有的行
  • FULL JOIN：只要其中一個表中存在匹配，則返回行

首先，連線的結果可以在邏輯上看作是由SELECT語句指定的列組成的新表。

左連線與右連線的左右指的是以兩張表中的哪一張為基準，它們都是外連線。

外連線就好像是為非基準表新增了一行全為空值的萬能行，用來與基準表中找不到匹配的行進行匹配。假設兩個沒有空值的表進行左連線，左表是基準表，左表的所有行都出現在結果中，右表則可能因為無法與基準表匹配而出現是空值的欄位。

這部分主要涉及的是表連線的邏輯問題，教程主講語法的話這部分確實需要自己再去搜資料

來源：《資料庫系統原理教程》，王珊，陳紅編著，P86

得到的結果數：

inner join <= min(left join, right join)
full join >= max(left join, right join)
當 inner join < min(left join, right join) 時， full join > max(left join, right join)

SQL INNER JOIN 關鍵字

SQL INNER JOIN 關鍵字

INNER JOIN 關鍵字在表中存在至少一個匹配時返回行。

SQL INNER JOIN 語法

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

註釋：INNER JOIN 與 JOIN 是相同的。

SQL INNER JOIN

演示資料庫

在本教程中，我們將使用 RUNOOB 樣本資料庫。

下面是選自 "Websites" 表的資料：

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘寶          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鳥教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

下面是 "access_log" 網站訪問記錄表的資料：

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

SQL INNER JOIN 例項

下面的 SQL 語句將返回所有網站的訪問記錄：

例項

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count;

執行以上 SQL 輸出結果如下：

註釋：INNER JOIN 關鍵字在表中存在至少一個匹配時返回行。如果 "Websites" 表中的行在 "access_log" 中沒有匹配，則不會列出這些行。

在使用 left jion 時，on 和 where 條件的區別如下：

  •  1、 on 條件是在生成臨時表時使用的條件，它不管 on 中的條件是否為真，都會返回左邊表中的記錄。
  •  2、where 條件是在臨時表生成好後，再對臨時表進行過濾的條件。這時已經沒有 left join 的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉。

SQL LEFT JOIN 關鍵字

LEFT JOIN 關鍵字從左表（table1）返回所有的行，即使右表（table2）中沒有匹配。如果右表中沒有匹配，則結果為 NULL。

SQL LEFT JOIN 語法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

註釋：在某些資料庫中，LEFT JOIN 稱為 LEFT OUTER JOIN。

SQL LEFT JOIN

演示資料庫

在本教程中，我們將使用 RUNOOB 樣本資料庫。

下面是選自 "Websites" 表的資料：

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘寶          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鳥教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

下面是 "access_log" 網站訪問記錄表的資料：

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

SQL LEFT JOIN 例項

下面的 SQL 語句將返回所有網站及他們的訪問量（如果有的話）。

以下例項中我們把 Websites 作為左表，access_log 作為右表：

例項

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

執行以上 SQL 輸出結果如下：

註釋：LEFT JOIN 關鍵字從左表（Websites）返回所有的行，即使右表（access_log）中沒有匹配。

SQL RIGHT JOIN 關鍵字

SQL RIGHT JOIN 關鍵字

RIGHT JOIN 關鍵字從右表（table2）返回所有的行，即使左表（table1）中沒有匹配。如果左表中沒有匹配，則結果為 NULL。

SQL RIGHT JOIN 語法

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

註釋：在某些資料庫中，RIGHT JOIN 稱為 RIGHT OUTER JOIN。

SQL RIGHT JOIN

演示資料庫

在本教程中，我們將使用 RUNOOB 樣本資料庫。

下面是選自 "Websites" 表的資料：

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘寶          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鳥教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

下面是 "access_log" 網站訪問記錄表的資料：

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

 

SQL RIGHT JOIN 例項

下面的 SQL 語句將返回網站的訪問記錄。

以下例項中我們把 access_log 作為左表，Websites 作為右表：

例項

SELECT Websites.name, access_log.count, access_log.date FROM access_log RIGHT JOIN Websites ON access_log.site_id=Websites.id ORDER BY access_log.count DESC;

執行以上 SQL 輸出結果如下：

註釋：RIGHT JOIN 關鍵字從右表（Websites）返回所有的行，即使左表（access_log）中沒有匹配。

SQL FULL OUTER JOIN 關鍵字

SQL FULL OUTER JOIN 關鍵字

FULL OUTER JOIN 關鍵字只要左表（table1）和右表（table2）其中一個表中存在匹配，則返回行.

FULL OUTER JOIN 關鍵字結合了 LEFT JOIN 和 RIGHT JOIN 的結果。

SQL FULL OUTER JOIN 語法

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

SQL FULL OUTER JOIN

演示資料庫

在本教程中，我們將使用 RUNOOB 樣本資料庫。

下面是選自 "Websites" 表的資料：

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘寶          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鳥教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

下面是 "access_log" 網站訪問記錄表的資料：

+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

SQL FULL OUTER JOIN 例項

下面的 SQL 語句選取所有網站訪問記錄。

MySQL中不支援 FULL OUTER JOIN，你可以在 SQL Server 測試以下例項。

例項

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

註釋：FULL OUTER JOIN 關鍵字返回左表（Websites）和右表（access_log）中所有的行。如果 "Websites" 表中的行在 "access_log" 中沒有匹配或者 "access_log" 表中的行在 "Websites" 表中沒有匹配，也會列出這些行。

 

相關文章