mysql內(nèi)連接和外連接的區(qū)別：內(nèi)連接會取出連接表中匹配到的數(shù)據(jù)，匹配不到的不保留；而外連接會取出連接表中匹配到的數(shù)據(jù)，匹配不到的也會保留，其值為NULL。
本教程操作環(huán)境：windows7系統(tǒng)、mysql8版本、Dell G3電腦。
區(qū)別
- 內(nèi)連接（inner join）：取出連接表中匹配到的數(shù)據(jù)，匹配不到的不保留
- 外連接（outer join）：取出連接表中匹配到的數(shù)據(jù)，匹配不到的也會保留，其值為NULL
示例表
users表
mysql>?select?*?from?users; +----+-------+ |?id?|?name??| +----+-------+ |??1?|?john??| |??2?|?May???| |??3?|?Lucy??| |??4?|?Jack??| |??5?|?James?| +----+-------+ 5?rows?in?set?(0.00?sec)
topics表
mysql>?select?*?from?topics; +----+---------------------------------------+---------+ |?id?|?title?????????????????????????????????|?user_id?| +----+---------------------------------------+---------+ |??1?|??Hello?world??????????????????????????|???????1?| |??2?|?PHP?is?the?best?language?in?the?world?|???????2?| |??3?|?Laravel?artist????????????????????????|???????6?| +----+---------------------------------------+---------+ 3?rows?in?set?(0.00?sec)
內(nèi)連接(inner join)
- 示例
mysql>?select?*?from?users?as?u?inner?join?topics?as?t?on?u.id=t.user_id; +----+------+----+---------------------------------------+---------+ |?id?|?name?|?id?|?title?????????????????????????????????|?user_id?| +----+------+----+---------------------------------------+---------+ |??1?|?john?|??1?|??Hello?world??????????????????????????|???????1?| |??2?|?May??|??2?|?PHP?is?the?best?language?in?the?world?|???????2?| +----+------+----+---------------------------------------+---------+ 2?rows?in?set?(0.00?sec)
inner可以省略，as是給表起別名，也可以省略
mysql>?select?*?from?users?u?join?topics?t?on?u.id=t.user_id; +----+------+----+---------------------------------------+---------+ |?id?|?name?|?id?|?title?????????????????????????????????|?user_id?| +----+------+----+---------------------------------------+---------+ |??1?|?john?|??1?|??Hello?world??????????????????????????|???????1?| |??2?|?May??|??2?|?PHP?is?the?best?language?in?the?world?|???????2?| +----+------+----+---------------------------------------+---------+ 2?rows?in?set?(0.00?sec)
以上兩句等價于
mysql>?select?*?from?users,topics?where?users.id=topics.user_id; +----+------+----+---------------------------------------+---------+ |?id?|?name?|?id?|?title?????????????????????????????????|?user_id?| +----+------+----+---------------------------------------+---------+ |??1?|?john?|??1?|??Hello?world??????????????????????????|???????1?| |??2?|?May??|??2?|?PHP?is?the?best?language?in?the?world?|???????2?| +----+------+----+---------------------------------------+---------+ 2?rows?in?set?(0.00?sec)
外連接(outer join)
- 左外連接（left outer join）：以左邊的表為主表
- 右外連接（right outer join）：以右邊的表為主表
以某一個表為主表，進(jìn)行關(guān)聯(lián)查詢，不管能不能關(guān)聯(lián)的上，主表的數(shù)據(jù)都會保留，關(guān)聯(lián)不上的以NULL顯示
通俗解釋就是：先拿出主表的所有數(shù)據(jù)，然后到關(guān)聯(lián)的那張表去找有沒有符合關(guān)聯(lián)條件的數(shù)據(jù)，如果有，正常顯示，如果沒有，顯示為NULL
示例
mysql>?select?*?from?users?as?u?left?join?topics?as?t?on?u.id=t.user_id; +----+-------+------+---------------------------------------+---------+ |?id?|?name??|?id???|?title?????????????????????????????????|?user_id?| +----+-------+------+---------------------------------------+---------+ |??1?|?john??|????1?|??Hello?world??????????????????????????|???????1?| |??2?|?May???|????2?|?PHP?is?the?best?language?in?the?world?|???????2?| |??3?|?Lucy??|?NULL?|?NULL??????????????????????????????????|????NULL?| |??4?|?Jack??|?NULL?|?NULL??????????????????????????????????|????NULL?| |??5?|?James?|?NULL?|?NULL??????????????????????????????????|????NULL?| +----+-------+------+---------------------------------------+---------+ 5?rows?in?set?(0.00?sec)
等價于以下，只是字段的位置不一樣
mysql>?select?*?from?topics?as?t?right?join?users?as?u?on?u.id=t.user_id; +------+---------------------------------------+---------+----+-------+ |?id???|?title?????????????????????????????????|?user_id?|?id?|?name??| +------+---------------------------------------+---------+----+-------+ |????1?|??Hello?world??????????????????????????|???????1?|??1?|?john??| |????2?|?PHP?is?the?best?language?in?the?world?|???????2?|??2?|?May???| |?NULL?|?NULL??????????????????????????????????|????NULL?|??3?|?Lucy??| |?NULL?|?NULL??????????????????????????????????|????NULL?|??4?|?Jack??| |?NULL?|?NULL??????????????????????????????????|????NULL?|??5?|?James?| +------+---------------------------------------+---------+----+-------+ 5?rows?in?set?(0.00?sec)
左外連接和右外連接是相對的，主要就是以哪個表為主表去進(jìn)行關(guān)聯(lián)
【相關(guān)推薦：mysql視頻教程】
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
喜歡就支持一下吧
相關(guān)推薦
