MySql中JOIN的用法示例詳解
更新時間：2022年03月24日 10:11:32 作者：一只小逸白
join顧名思義就是連接兩張表，大致分為內(nèi)連接，外連接，右連接，左連接，自然連接，今天通過本文給大家介紹MySql中JOIN的用法詳解，感興趣的朋友一起看看吧
上次面試被問到JOIN，自己都已經(jīng)忘了課堂上講的笛卡爾積那些就是JOIN，最近重新復習了一遍
JOIN的含義就如英文單詞“join”一樣，連接兩張表，大致分為內(nèi)連接，外連接，右連接，左連接，自然連接。
先創(chuàng)建兩個表，下面用于示例
CREATE TABLE t_blog(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(50),
typeId INT
);
SELECT * FROM t_blog;
+----+-------+--------+
| id | title | typeId |
+----+-------+--------+
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
| 4 | ddd | 4 |
| 5 | eee | 4 |
| 6 | fff | 3 |
| 7 | ggg | 2 |
| 8 | hhh | NULL |
| 9 | iii | NULL |
| 10 | jjj | NULL |
+----+-------+--------+
-- 博客的類別
CREATE TABLE t_type(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
SELECT * FROM t_type;
+----+------------+
| id | name |
+----+------------+
| 1 | C++ |
| 2 | C |
| 3 | Java |
| 4 | C# |
| 5 | Javascript |
+----+------------+
笛卡爾積：CROSS JOIN
要理解各種JOIN首先要理解笛卡爾積。笛卡爾積就是將A表的每一條記錄與B表的每一條記錄強行拼在一起。所以，如果A表有n條記錄，B表有m條記錄，笛卡爾積產(chǎn)生的結(jié)果就會產(chǎn)生n*m條記錄。下面的例子，t_blog有10條記錄，t_type有5條記錄，所有他們倆的笛卡爾積有50條記錄。有五種產(chǎn)生笛卡爾積的方式如下。
SELECT * FROM t_blog CROSS JOIN t_type;
SELECT * FROM t_blog INNER JOIN t_type;
SELECT * FROM t_blog,t_type;
SELECT * FROM t_blog NATURE JOIN t_type;
select * from t_blog NATURA join t_type;
+----+-------+--------+----+------------+
| id | title | typeId | id | name |
+----+-------+--------+----+------------+
| 1 | aaa | 1 | 1 | C++ |
| 1 | aaa | 1 | 2 | C |
| 1 | aaa | 1 | 3 | Java |
| 1 | aaa | 1 | 4 | C# |
| 1 | aaa | 1 | 5 | Javascript |
| 2 | bbb | 2 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 2 | bbb | 2 | 3 | Java |
| 2 | bbb | 2 | 4 | C# |
| 2 | bbb | 2 | 5 | Javascript |
| 3 | ccc | 3 | 1 | C++ |
| 3 | ccc | 3 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 3 | ccc | 3 | 4 | C# |
| 3 | ccc | 3 | 5 | Javascript |
| 4 | ddd | 4 | 1 | C++ |
| 4 | ddd | 4 | 2 | C |
| 4 | ddd | 4 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 4 | ddd | 4 | 5 | Javascript |
| 5 | eee | 4 | 1 | C++ |
| 5 | eee | 4 | 2 | C |
| 5 | eee | 4 | 3 | Java |
| 5 | eee | 4 | 4 | C# |
| 5 | eee | 4 | 5 | Javascript |
| 6 | fff | 3 | 1 | C++ |
| 6 | fff | 3 | 2 | C |
| 6 | fff | 3 | 3 | Java |
| 6 | fff | 3 | 4 | C# |
| 6 | fff | 3 | 5 | Javascript |
| 7 | ggg | 2 | 1 | C++ |
| 7 | ggg | 2 | 2 | C |
| 7 | ggg | 2 | 3 | Java |
| 7 | ggg | 2 | 4 | C# |
| 7 | ggg | 2 | 5 | Javascript |
| 8 | hhh | NULL | 1 | C++ |
| 8 | hhh | NULL | 2 | C |
| 8 | hhh | NULL | 3 | Java |
| 8 | hhh | NULL | 4 | C# |
| 8 | hhh | NULL | 5 | Javascript |
| 9 | iii | NULL | 1 | C++ |
| 9 | iii | NULL | 2 | C |
| 9 | iii | NULL | 3 | Java |
| 9 | iii | NULL | 4 | C# |
| 9 | iii | NULL | 5 | Javascript |
| 10 | jjj | NULL | 1 | C++ |
| 10 | jjj | NULL | 2 | C |
| 10 | jjj | NULL | 3 | Java |
| 10 | jjj | NULL | 4 | C# |
| 10 | jjj | NULL | 5 | Javascript |
+----+-------+--------+----+------------+
內(nèi)連接：INNER JOIN
內(nèi)連接INNER JOIN是最常用的連接操作。從數(shù)學的角度講就是求兩個表的交集，從笛卡爾積的角度講就是從笛卡爾積中挑出ON子句條件成立的記錄。有INNER JOIN，WHERE（等值連接），STRAIGHT_JOIN,JOIN(省略INNER)四種寫法。
SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId=t_type.id;
SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id;
SELECT * FROM t_blog STRAIGHT_JOIN t_type ON t_blog.typeId=t_type.id; --注意STRIGHT_JOIN有個下劃線
SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id;
+----+-------+--------+----+------+
| id | title | typeId | id | name |
+----+-------+--------+----+------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 7 | ggg | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 6 | fff | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
+----+-------+--------+----+------+
左連接：LEFT JOIN
左連接LEFT JOIN的含義就是求兩個表的交集外加左表剩下的數(shù)據(jù)。依舊從笛卡爾積的角度講，就是先從笛卡爾積中挑出ON子句條件成立的記錄，然后加上左表中剩余的記錄（見最后三條）。
SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id;
+----+-------+--------+------+------+
| id | title | typeId | id | name |
+----+-------+--------+------+------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 7 | ggg | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 6 | fff | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
| 8 | hhh | NULL | NULL | NULL |
| 9 | iii | NULL | NULL | NULL |
| 10 | jjj | NULL | NULL | NULL |
+----+-------+--------+------+------+
右連接：RIGHT JOIN
同理右連接RIGHT JOIN就是求兩個表的交集外加右表剩下的數(shù)據(jù)。再次從笛卡爾積的角度描述，右連接就是從笛卡爾積中挑出ON子句條件成立的記錄，然后加上右表中剩余的記錄（見最后一條）。
SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
+------+-------+--------+----+------------+
| id | title | typeId | id | name |
+------+-------+--------+----+------------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
| 6 | fff | 3 | 3 | Java |
| 7 | ggg | 2 | 2 | C |
| NULL | NULL | NULL | 5 | Javascript |
+------+-------+--------+----+------------+
外連接：OUTER JOIN
外連接就是求兩個集合的并集。從笛卡爾積的角度講就是從笛卡爾積中挑出ON子句條件成立的記錄，然后加上左表中剩余的記錄，最后加上右表中剩余的記錄。另外MySQL不支持OUTER JOIN，但是我們可以對左連接和右連接的結(jié)果做UNION操作來實現(xiàn)。
SELECT * FROM t_blog LEFT JOIN t_type ON t_blog.typeId=t_type.id
UNION
SELECT * FROM t_blog RIGHT JOIN t_type ON t_blog.typeId=t_type.id;
+------+-------+--------+------+------------+
| id | title | typeId | id | name |
+------+-------+--------+------+------------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 7 | ggg | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 6 | fff | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
| 8 | hhh | NULL | NULL | NULL |
| 9 | iii | NULL | NULL | NULL |
| 10 | jjj | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | Javascript |
+------+-------+--------+------+------------+
USING子句
MySQL中連接SQL語句中，ON子句的語法格式為：table1.column_name = table2.column_name。當模式設(shè)計對聯(lián)接表的列采用了相同的命名樣式時，就可以使用 USING 語法來簡化 ON 語法，格式為：USING(column_name)。
所以，USING的功能相當于ON，區(qū)別在于USING指定一個屬性名用于連接兩個表，而ON指定一個條件。另外，SELECT *時，USING會去除USING指定的列，而ON不會。實例如下。
SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId =t_type.id;
+----+-------+--------+----+------+
| id | title | typeId | id | name |
+----+-------+--------+----+------+
| 1 | aaa | 1 | 1 | C++ |
| 2 | bbb | 2 | 2 | C |
| 7 | ggg | 2 | 2 | C |
| 3 | ccc | 3 | 3 | Java |
| 6 | fff | 3 | 3 | Java |
| 4 | ddd | 4 | 4 | C# |
| 5 | eee | 4 | 4 | C# |
+----+-------+--------+----+------+
SELECT * FROM t_blog INNER JOIN t_type USING(typeId);
ERROR 1054 (42S22): Unknown column 'typeId' in 'from clause'
SELECT * FROM t_blog INNER JOIN t_type USING(id); -- 應為t_blog的typeId與t_type的id不同名，無法用Using，這里用id代替下。
+----+-------+--------+------------+
| id | title | typeId | name |
+----+-------+--------+------------+
| 1 | aaa | 1 | C++ |
| 2 | bbb | 2 | C |
| 3 | ccc | 3 | Java |
| 4 | ddd | 4 | C# |
| 5 | eee | 4 | Javascript |
+----+-------+--------+------------+
自然連接：NATURE JOIN
自然連接就是USING子句的簡化版，它找出兩個表中相同的列作為連接條件進行連接。有左自然連接，右自然連接和普通自然連接之分。在t_blog和t_type示例中，兩個表相同的列是id，所以會拿id作為連接條件。
另外千萬分清下面三條語句的區(qū)別 。
自然連接:SELECT * FROM t_blog NATURAL JOIN t_type;
笛卡爾積:SELECT * FROM t_blog NATURA JOIN t_type;
笛卡爾積:SELECT * FROM t_blog NATURE JOIN t_type;
SELECT * FROM t_blog NATURAL JOIN t_type;
SELECT t_blog.id,title,typeId,t_type.name FROM t_blog,t_type WHERE t_blog.id=t_type.id;
SELECT t_blog.id,title,typeId,t_type.name FROM t_blog INNER JOIN t_type ON t_blog.id=t_type.id;
SELECT t_blog.id,title,typeId,t_type.name FROM t_blog INNER JOIN t_type USING(id);
+----+-------+--------+------------+
| id | title | typeId | name |
| 1 | aaa | 1 | C++ |
| 2 | bbb | 2 | C |
| 3 | ccc | 3 | Java |
| 4 | ddd | 4 | C# |
| 5 | eee | 4 | Javascript |
SELECT * FROM t_blog NATURAL LEFT JOIN t_type;
SELECT t_blog.id,title,typeId,t_type.name FROM t_blog LEFT JOIN t_type ON t_blog.id=t_type.id;
SELECT t_blog.id,title,typeId,t_type.name FROM t_blog LEFT JOIN t_type USING(id);
| 6 | fff | 3 | NULL |
| 7 | ggg | 2 | NULL |
| 8 | hhh | NULL | NULL |
| 9 | iii | NULL | NULL |
| 10 | jjj | NULL | NULL |
SELECT * FROM t_blog NATURAL RIGHT JOIN t_type;
SELECT t_blog.id,title,typeId,t_type.name FROM t_blog RIGHT JOIN t_type ON t_blog.id=t_type.id;
SELECT t_blog.id,title,typeId,t_type.name FROM t_blog RIGHT JOIN t_type USING(id);
+----+------------+-------+--------+
| id | name | title | typeId |
| 1 | C++ | aaa | 1 |
| 2 | C | bbb | 2 |
| 3 | Java | ccc | 3 |
| 4 | C# | ddd | 4 |
| 5 | Javascript | eee | 4 |到此這篇關(guān)于MySql中JOIN的用法的文章就介紹到這了,更多相關(guān)MySql JOIN用法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了解決mysql 默認排序規(guī)則的坑，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看不看2021-02-02
這篇文章主要給大家介紹了關(guān)于Mysql explain用法與結(jié)果分析的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-01-01
MySQL與PHP的基礎(chǔ)與應用專題之數(shù)據(jù)查詢
MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL?AB?公司開發(fā)，屬于?Oracle?旗下產(chǎn)品。MySQL?是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一，本系列將帶你掌握php與mysql的基礎(chǔ)應用，本篇從數(shù)據(jù)的查詢開始2022-02-02
在本篇文章里小編給大家整理的是一篇關(guān)于解決mysql無法關(guān)閉的問題的相關(guān)內(nèi)容，需要的朋友們可以參考下。2020-08-08
這篇文章主要介紹了如何干涉MySQL優(yōu)化器使用hash?join,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-09-09
MySQL的自增id都定義了初始值，然后不斷加步長。雖然自然數(shù)沒有上限，但定義了表示這個數(shù)的字節(jié)長度，那自增id用完，會怎么樣？本文就來介紹一下2021-08-08
MySQL essential版本和普通版本有什么區(qū)別？
安裝mysql的朋友可能會發(fā)現(xiàn)有時候我們看到essential版本，究竟與其它mysql版本有什么區(qū)別呢，這里簡單介紹下2013-06-06
這篇文章主要介紹了mysql視圖之管理視圖,結(jié)合實例形式詳細分析了mysql視圖增刪改查操作具體實現(xiàn)技巧與相關(guān)操作注意事項,需要的朋友可以參考下2019-12-12
有些新手朋友對MySQL定時器開啟、調(diào)用不是很熟悉，本人整理測試一些，拿出來和大家分享一下，希望可以幫助你們2012-12-12
在本篇文章中我們給大家講述了mysql中影響性能的因素以及相關(guān)知識點內(nèi)容，有興趣的朋友參考下。2018-09-09