問(wèn)題描述

我想知道以下內(nèi)容:

• 如何從我的數(shù)據(jù)庫(kù)中的多個(gè)表中獲取數(shù)據(jù)?
• 有哪些方法可以做到這一點(diǎn)?
• 什么是聯(lián)接和聯(lián)合?它們之間有何不同?
• 與其他的相比，我應(yīng)該什么時(shí)候使用它們?

我打算在我的(例如 - PHP)應(yīng)用程序中使用它，但不想對(duì)數(shù)據(jù)庫(kù)運(yùn)行多個(gè)查詢，我有哪些選項(xiàng)可以在單個(gè)查詢中從多個(gè)表中獲取數(shù)據(jù)?

注意:我寫(xiě)這篇文章是因?yàn)槲蚁Ｍ軌蜴溄拥疥P(guān)于我在 PHP 隊(duì)列中經(jīng)常遇到的眾多問(wèn)題的寫(xiě)得很好的指南，所以當(dāng)我發(fā)布一個(gè)回答.

答案涵蓋以下內(nèi)容:

1. 第 1 部分 - 聯(lián)接和工會(huì)
2. 第 2 部分 - 子查詢
3. 第 3 部分 - 技巧和高效的代碼
4. 第 4 部分 - From 子句中的子查詢
5. 第 5 部分 - 約翰的詭計(jì)混雜

第 1 部分 - 聯(lián)接和聯(lián)合

這個(gè)答案涵蓋:

1. 第 1 部分
   • 使用內(nèi)連接連接兩個(gè)或多個(gè)表(請(qǐng)參閱維基百科條目了解更多信息)
   • 如何使用聯(lián)合查詢
   • 左外連接和右外連接(這個(gè) stackOverflow 答案非常適合描述連接類型)
   • 交叉查詢(以及如果您的數(shù)據(jù)庫(kù)不支持它們，如何重現(xiàn)它們) - 這是 SQL-Server 的一個(gè)功能 (查看信息)和 莉>
2. 第 2 部分
   • 子查詢 - 它們是什么，可以在哪里使用以及需要注意什么
   • 笛卡爾加入 AKA - 哦，痛苦！

有多種方法可以從數(shù)據(jù)庫(kù)中的多個(gè)表中檢索數(shù)據(jù).在這個(gè)答案中，我將使用 ANSI-92 連接語(yǔ)法.這可能與其他一些使用舊的 ANSI-89 語(yǔ)法的教程不同(如果你習(xí)慣了 89，可能看起來(lái)不那么直觀 - 但我只能說(shuō)嘗試一下)，因?yàn)樗?em>當(dāng)查詢開(kāi)始變得更復(fù)雜時(shí)，更容易理解.為什么要使用它?有性能提升嗎?簡(jiǎn)短的回答是否定的，但是一旦你習(xí)慣了它就更容易閱讀了.使用這種語(yǔ)法閱讀其他人編寫(xiě)的查詢會(huì)更容易.

我還將使用一個(gè)小型車(chē)場(chǎng)的概念，它有一個(gè)數(shù)據(jù)庫(kù)來(lái)跟蹤它有哪些可用的汽車(chē).所有者已聘請(qǐng)您作為他的 IT 計(jì)算機(jī)人員，并希望您能夠毫不猶豫地將他要求的數(shù)據(jù)交給他.

我制作了一些最終表將使用的查找表.這將為我們提供一個(gè)合理的模型來(lái)工作.首先，我將針對(duì)具有以下結(jié)構(gòu)的示例數(shù)據(jù)庫(kù)運(yùn)行查詢.我會(huì)試著想想在開(kāi)始時(shí)犯的常見(jiàn)錯(cuò)誤，并解釋它們出了什么問(wèn)題——當(dāng)然也會(huì)展示如何糾正它們.

第一個(gè)表格只是一個(gè)顏色列表，以便我們知道車(chē)場(chǎng)里有什么顏色.

mysql>創(chuàng)建表顏色(id int(3) not null auto_increment 主鍵，->顏色變量(15)，油漆變量(10))；查詢正常，0 行受影響(0.01 秒)mysql>從顏色顯示列；+-------+-------------+------+-----+---------+-----------------+|領(lǐng)域 |類型 |空 |鑰匙 |默認(rèn) |額外 |+-------+-------------+------+-----+---------+-----------------+|身份證 |整數(shù)(3) |否 |PRI |空 |自動(dòng)增量||顏色 |varchar(15) |是 ||空 |||油漆|varchar(10) |是 ||空 ||+-------+-------------+------+-----+---------+-----------------+3 行(0.01 秒)mysql>插入顏色(顏色，油漆)值('紅色'，'金屬')，->('綠色', '光澤'), ('藍(lán)色', '金屬'),->('白色' '光澤'), ('黑色' '光澤');查詢正常，5 行受影響(0.00 秒)記錄:5 重復(fù):0 警告:0mysql>從顏色中選擇*；+----+-------+----------+|身份證 |顏色 |油漆|+----+-------+----------+|1 |紅色 |金屬 ||2 |綠色 |光澤 ||3 |藍(lán)色 |金屬 ||4 |白色 |光澤 ||5 |黑色 |光澤 |+----+-------+----------+5 行(0.00 秒)

品牌表標(biāo)識(shí)了caryard 可能銷(xiāo)售的不同品牌汽車(chē).

mysql>創(chuàng)建表品牌(id int(3) not null auto_increment 主鍵，->品牌 varchar(15));查詢正常，0 行受影響(0.01 秒)mysql>顯示品牌欄目；+-------+-------------+------+-----+---------+-----------------+|領(lǐng)域 |類型 |空 |鑰匙 |默認(rèn) |額外 |+-------+-------------+------+-----+---------+-----------------+|身份證 |整數(shù)(3) |否 |PRI |空 |自動(dòng)增量||品牌 |varchar(15) |是 ||空 ||+-------+-------------+------+-----+---------+-----------------+2 行(0.01 秒)mysql>插入品牌 (brand) 值 ('Ford'), ('Toyota'),->('Nissan'), ('Smart'), ('BMW');查詢正常，5 行受影響(0.00 秒)記錄:5 重復(fù):0 警告:0mysql>從品牌中選擇*；+----+--------+|身份證 |品牌 |+----+--------+|1 |福特 ||2 |豐田 ||3 |日產(chǎn) ||4 |智能 ||5 |寶馬 |+----+--------+5 行(0.00 秒)

模型表將涵蓋不同類型的汽車(chē)，使用不同的汽車(chē)類型而不是實(shí)際的汽車(chē)模型會(huì)更簡(jiǎn)單.

mysql>創(chuàng)建表模型(id int(3) not null auto_increment 主鍵，->模型 varchar(15));查詢正常，0 行受影響(0.01 秒)mysql>顯示模型中的列；+-------+-------------+------+-----+---------+-----------------+|領(lǐng)域 |類型 |空 |鑰匙 |默認(rèn) |額外 |+-------+-------------+------+-----+---------+-----------------+|身份證 |整數(shù)(3) |否 |PRI |空 |自動(dòng)增量||模型 |varchar(15) |是 ||空 ||+-------+-------------+------+-----+---------+-----------------+2 行(0.00 秒)mysql>插入模型 (model) 值 ('Sports'), ('Sedan'), ('4WD'), ('Luxury');查詢正常，4 行受影響(0.00 秒)記錄:4 重復(fù):0 警告:0mysql>從模型中選擇 *；+----+--------+|身份證 |模型 |+----+--------+|1 |體育 ||2 |轎車(chē) ||3 |四驅(qū) ||4 |豪華 |+----+--------+4 行(0.00 秒)

最后，將所有這些其他表捆綁在一起，將所有東西捆綁在一起的表.ID 字段實(shí)際上是用于識(shí)別汽車(chē)的唯一批號(hào).

mysql>創(chuàng)建表汽車(chē)(id int(3) not null auto_increment 主鍵，->顏色 int(3), 品牌 int(3), 型號(hào) int(3));查詢正常，0 行受影響(0.01 秒)mysql>顯示來(lái)自汽車(chē)的列；+-------+--------+------+-----+---------+----------------+|領(lǐng)域 |類型 |空 |鑰匙 |默認(rèn) |額外 |+-------+--------+------+-----+---------+----------------+|身份證 |整數(shù)(3) |否 |PRI |空 |自動(dòng)增量||顏色 |整數(shù)(3) |是 ||空 |||品牌 |整數(shù)(3) |是 ||空 |||模型 |整數(shù)(3) |是 ||空 ||+-------+--------+------+-----+---------+----------------+4 行(0.00 秒)mysql>插入汽車(chē)(顏色，品牌，型號(hào))值(1,2,1)，(3,1,2)，(5,3,1)，->(4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4),5,1);查詢正常，10 行受影響(0.00 秒)記錄:10 重復(fù):0 警告:0mysql>從汽車(chē)中選擇 *；+----+-------+-------+-------+|身份證 |顏色 |品牌 |模型 |+----+-------+-------+-------+|1 |1 |2 |1 ||2 |3 |1 |2 ||3 |5 |3 |1 ||4 |4 |4 |2 ||5 |2 |2 |3 ||6 |3 |5 |4 ||7 |4 |1 |3 ||8 |2 |2 |1 ||9 |5 |2 |3 ||10 |4 |5 |1 |+----+-------+-------+-------+10 行(0.00 秒)

這將為我們提供足夠的數(shù)據(jù)(我希望)來(lái)涵蓋以下不同類型連接的示例，并提供足夠的數(shù)據(jù)使它們值得.

因此，老板想知道他擁有的所有跑車(chē)的 ID.

這是一個(gè)簡(jiǎn)單的兩表連接.我們有一個(gè)表來(lái)標(biāo)識(shí)模型和包含可用庫(kù)存的表.可以看到，cars表的model列中的數(shù)據(jù)與cars表的models列相關(guān)> 我們有桌子.現(xiàn)在，我們知道模型表的 1 用于 Sports 的 ID，所以讓我們編寫(xiě)連接.

選擇ID，模型從汽車(chē)加入模型型號(hào)=ID

所以這個(gè)查詢看起來(lái)不錯(cuò)吧?我們已經(jīng)確定了兩個(gè)表并包含了我們需要的信息，并使用了一個(gè)連接來(lái)正確識(shí)別要連接的列.

ERROR 1052 (23000): 字段列表中的ID"列不明確

哦，不！我們的第一個(gè)查詢出錯(cuò)了！是的，它是一個(gè)李子.您會(huì)看到，查詢確實(shí)獲得了正確的列，但其中一些列存在于兩個(gè)表中，因此數(shù)據(jù)庫(kù)對(duì)我們的實(shí)際含義和位置感到困惑.有兩種解決方案可以解決這個(gè)問(wèn)題.第一個(gè)很好很簡(jiǎn)單，我們可以使用 tableName.columnName 來(lái)告訴數(shù)據(jù)庫(kù)我們的意思，就像這樣:

選擇汽車(chē).ID,模型.model從汽車(chē)加入模型在cars.model=models.ID+----+--------+|身份證 |模型 |+----+--------+|1 |體育 ||3 |體育 ||8 |體育 ||10 |體育 ||2 |轎車(chē) ||4 |轎車(chē) ||5 |四驅(qū) ||7 |四驅(qū) ||9 |四驅(qū) ||6 |豪華 |+----+--------+10 行(0.00 秒)

另一個(gè)可能更常用，稱為表別名.這個(gè)例子中的表格有漂亮而簡(jiǎn)短的名字，但是輸入類似 KPI_DAILY_SALES_BY_DEPARTMENT 的東西可能會(huì)很快變老，所以一個(gè)簡(jiǎn)單的方法是給表格起這樣的昵稱:

選擇援助，b.模型從汽車(chē)加入模型 ba.model=b.ID

現(xiàn)在，回到請(qǐng)求.正如你所看到的，我們有我們需要的信息，但我們也有沒(méi)有被要求的信息，所以我們需要在語(yǔ)句中包含一個(gè) where 子句，以只獲取被要求的跑車(chē).由于我更喜歡??表別名方法而不是一遍又一遍地使用表名，所以從現(xiàn)在開(kāi)始我將堅(jiān)持使用它.

顯然，我們需要在查詢中添加一個(gè) where 子句.我們可以通過(guò) ID=1 或 model='Sports' 來(lái)識(shí)別跑車(chē).由于 ID 已編入索引和主鍵(而且它的鍵入次數(shù)較少)，因此讓我們?cè)诓樵冎惺褂盟?

選擇援助，b.模型從汽車(chē)加入模型 ba.model=b.ID在哪里b.ID=1+----+--------+|身份證 |模型 |+----+--------+|1 |體育 ||3 |體育 ||8 |體育 ||10 |體育 |+----+--------+4 行(0.00 秒)

賓果游戲！老板很高興.當(dāng)然，作為老板，從不滿足于他的要求，他查看信息，然后說(shuō)我也想要顏色.

好的，所以我們已經(jīng)編寫(xiě)了查詢的很大一部分，但是我們需要使用第三個(gè)表，即顏色.現(xiàn)在，我們的主信息表 cars 存儲(chǔ)了汽車(chē)顏色 ID，這會(huì)鏈接回顏色 ID 列.因此，以與原始類似的方式，我們可以加入第三個(gè)表:

選擇援助，b.模型從汽車(chē)加入模型 ba.model=b.ID加入顏色 ca.color=c.ID在哪里b.ID=1+----+--------+|身份證 |模型 |+----+--------+|1 |體育 ||3 |體育 ||8 |體育 ||10 |體育 |+----+--------+4 行(0.00 秒)

該死，雖然表已正確連接并且相關(guān)列已鏈接，但我們忘記從我們剛剛鏈接的新表中提取實(shí)際信息.

選擇援助，b.模型，c.顏色從汽車(chē)加入模型 ba.model=b.ID加入顏色 ca.color=c.ID在哪里b.ID=1+----+--------+-------+|身份證 |模型 |顏色 |+----+--------+-------+|1 |體育 |紅色 ||8 |體育 |綠色 ||10 |體育 |白色 ||3 |體育 |黑色 |+----+--------+-------+4 行(0.00 秒)

是的，那是我們暫時(shí)離開(kāi)的老板.現(xiàn)在，更詳細(xì)地解釋其中的一些內(nèi)容.如您所見(jiàn)，我們語(yǔ)句中的 from 子句鏈接了我們的主表(我經(jīng)常使用包含信息的表，而不是查找表或維度表.查詢將與所有表一樣正常工作切換了，但是當(dāng)我們?cè)趲讉€(gè)月后回到這個(gè)查詢來(lái)閱讀它時(shí)就沒(méi)那么有意義了，所以通常最好嘗試編寫(xiě)一個(gè)很好且易于理解的查詢 - 直觀地布局它，使用 nice縮進(jìn)，以便一切都盡可能清楚.如果您繼續(xù)教導(dǎo)他人，請(qǐng)嘗試將這些特征灌輸?shù)剿麄兊牟樵冎?- 特別是如果您要對(duì)它們進(jìn)行故障排除時(shí).

完全有可能以這種方式鏈接越來(lái)越多的表格.

選擇援助，b.模型，c.顏色從汽車(chē)加入模型 ba.model=b.ID加入顏色 ca.color=c.ID加盟品牌da.brand=d.ID在哪里b.ID=1

雖然我忘記在 join 語(yǔ)句中包含一個(gè)我們可能想要連接多個(gè)列的表，但這里有一個(gè)示例.如果 models 表具有品牌特定的型號(hào)，因此也有一個(gè)名為 brand 的列，該列鏈接回 上的 brands 表ID 字段，可以這樣做:

選擇援助，b.模型，c.顏色從汽車(chē)加入模型 ba.model=b.ID加入顏色 ca.color=c.ID加盟品牌da.brand=d.IDb.brand=d.ID在哪里b.ID=1

您可以看到，上面的查詢不僅將連接表鏈接到主 cars 表，還指定了已連接表之間的連接.如果沒(méi)有這樣做，則結(jié)果稱為笛卡爾連接 - 這是 dba 代表不好.笛卡爾連接是一種返回行的連接，因?yàn)樾畔](méi)有告訴數(shù)據(jù)庫(kù)如何限制結(jié)果，因此查詢返回所有符合條件的行.

所以，舉一個(gè)笛卡爾連接的例子，讓我們運(yùn)行以下查詢:

選擇援助，b.模型從汽車(chē)加入模型 b+----+--------+|身份證 |模型 |+----+--------+|1 |體育 ||1 |轎車(chē) ||1 |四驅(qū) ||1 |豪華 ||2 |體育 ||2 |轎車(chē) ||2 |四驅(qū) ||2 |豪華 ||3 |體育 ||3 |轎車(chē) ||3 |四驅(qū) ||3 |豪華 ||4 |體育 ||4 |轎車(chē) ||4 |四驅(qū) ||4 |豪華 ||5 |體育 ||5 |轎車(chē) ||5 |四驅(qū) ||5 |豪華 ||6 |體育 ||6 |轎車(chē) ||6 |四驅(qū) ||6 |豪華 ||7 |體育 ||7 |轎車(chē) ||7 |四驅(qū) ||7 |豪華 ||8 |體育 ||8 |轎車(chē) ||8 |四驅(qū) ||8 |豪華 ||9 |體育 ||9 |轎車(chē) ||9 |四驅(qū) ||9 |豪華 ||10 |體育 ||10 |轎車(chē) ||10 |四驅(qū) ||10 |豪華 |+----+--------+40 行(0.00 秒)

天哪，太丑了.然而，就數(shù)據(jù)庫(kù)而言，它正是所要求的.在查詢中，我們要求 cars 的 ID 和 models 的 model.但是，因?yàn)槲覀儧](méi)有指定如何連接這些表，數(shù)據(jù)庫(kù)已經(jīng)將第一個(gè)表中的每一行與每一行進(jìn)行了匹配.第二張桌子.

好的，所以老板回來(lái)了，他又想了解更多信息.我想要相同的列表，但還包括四驅(qū)車(chē).

然而，這給了我們一個(gè)很好的借口來(lái)看看兩種不同的方法來(lái)實(shí)現(xiàn)這一點(diǎn).我們可以在 where 子句中添加另一個(gè)條件，如下所示:

選擇援助，b.模型，c.顏色從汽車(chē)加入模型 ba.model=b.ID加入顏色 ca.color=c.ID加盟品牌da.brand=d.ID在哪里b.ID=1或 b.ID=3

雖然上面的方法工作得很好，但讓我們換個(gè)角度看，這是展示 union 查詢?nèi)绾喂ぷ鞯囊粋€(gè)很好的借口.

我們知道以下將返回所有跑車(chē):

選擇援助，b.模型，c.顏色從汽車(chē)加入模型 ba.model=b.ID加入顏色 ca.color=c.ID加盟品牌da.brand=d.ID在哪里b.ID=1

以下將返回所有 4WD:

選擇援助，b.模型，c.顏色從汽車(chē)加入模型 ba.model=b.ID加入顏色 ca.color=c.ID加盟品牌da.brand=d.ID在哪里b.ID=3

因此，通過(guò)在它們之間添加 union all 子句，第二個(gè)查詢的結(jié)果將附加到第一個(gè)查詢的結(jié)果中.

選擇援助，b.模型，c.顏色從汽車(chē)加入模型 ba.model=b.ID加入顏色 ca.color=c.ID加盟品牌da.brand=d.ID在哪里b.ID=1聯(lián)合所有選擇援助，b.模型，c.顏色從汽車(chē)加入模型 ba.model=b.ID加入顏色 ca.color=c.ID加盟品牌da.brand=d.ID在哪里b.ID=3+----+--------+-------+|身份證 |模型 |顏色 |+----+--------+-------+|1 |體育 |紅色 ||8 |體育 |綠色 ||10 |體育 |白色 ||3 |體育 |黑色 ||5 |四驅(qū) |綠色 ||7 |四驅(qū) |白色 ||9 |四驅(qū) |黑色 |+----+--------+-------+7 行(0.00 秒)

如您所見(jiàn)，首先返回第一個(gè)查詢的結(jié)果，然后是第二個(gè)查詢的結(jié)果.

在這個(gè)例子中，簡(jiǎn)單地使用第一個(gè)查詢當(dāng)然會(huì)容易得多，但是 union 查詢對(duì)于特定情況可能非常有用.它們是從不容易連接在一起的表中返回特定結(jié)果的好方法 - 或者就此而言完全不相關(guān)的表.但是，有一些規(guī)則需要遵循.

• 第一個(gè)查詢中的列類型必須與下面所有其他查詢中的列類型相匹配.
• 第一個(gè)查詢中的列名稱將用于標(biāo)識(shí)整個(gè)結(jié)果集.
• 每個(gè)查詢中的列數(shù)必須相同.

現(xiàn)在，您可能想知道 區(qū)別在于使用union 和union all.union 查詢將刪除重復(fù)項(xiàng)，而 union all 不會(huì).這確實(shí)意味著在使用 union 而不是 union all 時(shí)，性能會(huì)受到很小的影響，但結(jié)果可能是值得的 - 我不會(huì)在此推測(cè)這類事情不過(guò).

關(guān)于這個(gè)注釋，這里可能需要注意一些額外的注釋.

• 如果我們想對(duì)結(jié)果進(jìn)行排序，我們可以使用 order by 但您不能再使用別名.在上面的查詢中，附加一個(gè) order by a.ID 會(huì)導(dǎo)致錯(cuò)誤 - 就結(jié)果而言，該列被稱為 ID 而不是 a.ID - 即使在兩個(gè)查詢中使用了相同的別名.
• 我們只能有一個(gè)order by語(yǔ)句，而且必須是最后一個(gè)語(yǔ)句.

對(duì)于接下來(lái)的示例，我將向我們的表中添加一些額外的行.

我已將 Holden 添加到品牌表中.我還在 cars 中添加了一行，其 color 值為 12 - 在顏色表中沒(méi)有引用.

好的，老板又回來(lái)了，大聲喊叫 - *我想統(tǒng)計(jì)我們攜帶的每個(gè)品牌以及其中的汽車(chē)數(shù)量！` - 典型的，我們只是討論了一個(gè)有趣的部分，然后老板想要更多的工作.

Rightyo，所以我們需要做的第一件事就是獲得可能品牌的完整列表.

選擇一個(gè)品牌從品牌+--------+|品牌 |+--------+|福特 ||豐田 ||日產(chǎn) ||智能 ||寶馬 ||霍爾頓 |+--------+6 行(0.00 秒)

現(xiàn)在，當(dāng)我們將其加入到我們的汽車(chē)表中時(shí)，我們得到以下結(jié)果:

選擇一個(gè)品牌從品牌加入汽車(chē) ba.ID=b.brand通過(guò)...分組一個(gè)品牌+--------+|品牌 |+--------+|寶馬 ||福特 ||日產(chǎn) ||智能 ||豐田 |+--------+5 行(0.00 秒)

這當(dāng)然是一個(gè)問(wèn)題 - 我們沒(méi)有看到任何提及我添加的可愛(ài)的 Holden 品牌.

這是因?yàn)檫B接在兩個(gè)表中查找匹配的行.由于Holden 類型的汽車(chē)中沒(méi)有數(shù)據(jù)，因此不會(huì)返回.這是我們可以使用 outer 連接的地方.這將返回所有一個(gè)表中的結(jié)果，無(wú)論它們?cè)诹硪粋€(gè)表中是否匹配:

選擇一個(gè)品牌從品牌左外連接車(chē) ba.ID=b.brand通過(guò)...分組一個(gè)品牌+--------+|品牌 |+--------+|寶馬 ||福特 ||霍爾頓 ||日產(chǎn) ||智能 ||豐田 |+--------+6 行(0.00 秒)

既然我們有了這個(gè)，我們可以添加一個(gè)可愛(ài)的聚合函數(shù)來(lái)計(jì)算并讓老板暫時(shí)擺脫困境.

選擇一個(gè)品牌，count(b.id) 作為 countOfBrand從品牌左外連接車(chē) ba.ID=b.brand通過(guò)...分組一個(gè)品牌+--------+--------------+|品牌 |countOfBrand |+--------+--------------+|寶馬 |2 ||福特 |2 ||霍爾頓 |0 ||日產(chǎn) |1 ||智能 |1 ||豐田 |5 |+--------+--------------+6 行(0.00 秒)

這樣，老板就躲開(kāi)了.

現(xiàn)在，為了更詳細(xì)地解釋這一點(diǎn)，外連接可以是 left 或 right 類型.Left 或 Right 定義完全包含哪個(gè)表.left outer join 將包含左側(cè)表中的所有行，而(你猜對(duì)了)right external join 將包含右側(cè)表中的所有結(jié)果進(jìn)入結(jié)果.

某些數(shù)據(jù)庫(kù)將允許完全外連接，這將從兩個(gè)表中帶回結(jié)果(無(wú)論是否匹配)，但并非所有數(shù)據(jù)庫(kù)都支持這種方式.

現(xiàn)在，我可能認(rèn)為此時(shí)此刻，您想知道是否可以在查詢中合并連接類型 - 答案是肯定的，您絕對(duì)可以.

選擇b.品牌，c.顏色，count(a.id) 作為 countOfBrand從汽車(chē)右外連接品牌 bb.ID=a.brand加入顏色 ca.color=c.ID通過(guò)...分組一個(gè)品牌，c.顏色+--------+-------+--------------+|品牌 |顏色 |countOfBrand |+--------+-------+--------------+|福特 |藍(lán)色 |1 ||福特 |白色 |1 ||豐田 |黑色 |1 ||豐田 |綠色 |2 ||豐田 |紅色 |1 ||日產(chǎn) |黑色 |1 ||智能 |白色 |1 ||寶馬 |藍(lán)色 |1 ||寶馬 |白色 |1 |+--------+-------+--------------+9 行(0.00 秒)

那么，為什么這不是預(yù)期的結(jié)果?這是因?yàn)殡m然我們選擇了從汽車(chē)到品牌的外部連接，但在連接到顏色中并沒(méi)有指定 - 所以特定的連接只會(huì)帶回兩個(gè)表中匹配的結(jié)果.

以下查詢可用于獲得我們預(yù)期的結(jié)果:

選擇一個(gè)品牌，c.顏色，count(b.id) 作為 countOfBrand從品牌左外連接車(chē) ba.ID=b.brand左外連接顏色 c在 b.color=c.ID 上通過(guò)...分組一個(gè)品牌，c.顏色+--------+-------+--------------+|品牌 |顏色 |countOfBrand |+--------+-------+--------------+|寶馬 |藍(lán)色 |1 ||寶馬 |白色 |1 ||福特 |藍(lán)色 |1 ||福特 |白色 |1 ||霍爾頓 |空 |0 ||日產(chǎn) |黑色 |1 ||智能 |白色 |1 ||豐田 |空 |1 ||豐田 |黑色 |1 ||豐田 |綠色 |2 ||豐田 |紅色 |1 |+--------+-------+--------------+11 行(0.00 秒)

如我們所見(jiàn)，我們?cè)诓樵冎杏袃蓚€(gè)外部聯(lián)接，結(jié)果按預(yù)期通過(guò).

現(xiàn)在，你問(wèn)的那些其他類型的連接怎么樣?交叉路口呢?

好吧，并非所有數(shù)據(jù)庫(kù)都支持 intersection，但幾乎所有數(shù)據(jù)庫(kù)都允許您通過(guò)連接(或至少結(jié)構(gòu)良好的 where 語(yǔ)句)創(chuàng)建交集.

Intersection 是一種連接類型，有點(diǎn)類似于上述的 union - 但區(qū)別在于它僅返回相同的數(shù)據(jù)行(并且我確實(shí)意味著相同)在聯(lián)合加入的各種單獨(dú)查詢之間.只會(huì)返回在各方面都相同的行.

一個(gè)簡(jiǎn)單的例子是這樣的:

選擇*從顏色在哪里ID＞2相交選擇*從顏色在哪里id<4

雖然普通的 union 查詢將返回表的所有行(第一個(gè)查詢返回 ID>2 上的任何內(nèi)容，第二個(gè)返回任何具有 ID<;4) 這將導(dǎo)致一個(gè)完整的集合，交叉查詢將只返回匹配 id=3 的行，因?yàn)樗鼭M足兩個(gè)條件.

現(xiàn)在，如果您的數(shù)據(jù)庫(kù)不支持 intersect 查詢，則可以使用以下查詢輕松完成上述操作:

選擇援助，a.顏色，a.油漆從顏色 a加入顏色 ba.ID=b.ID在哪里a.ID＞2b.ID＜4+----+-------+----------+|身份證 |顏色 |油漆|+----+-------+----------+|3 |藍(lán)色 |金屬 |+----+-------+----------+1 行(0.00 秒)

如果您希望使用本身不支持交集查詢的數(shù)據(jù)庫(kù)在兩個(gè)不同的表之間執(zhí)行交集，則需要在表的每一列上創(chuàng)建一個(gè)連接.>

I would like to know the following:

• how to get data from multiple tables in my database?
• what types of methods are there to do this?
• what are joins and unions and how are they different from one another?
• When should I use each one compared to the others?

I am planning to use this in my (for example - PHP) application, but don't want to run multiple queries against the database, what options do I have to get data from multiple tables in a single query?

Note: I am writing this as I would like to be able to link to a well written guide on the numerous questions that I constantly come across in the PHP queue, so I can link to this for further detail when I post an answer.

The answers cover off the following:

1. Part 1 - Joins and Unions
2. Part 2 - Subqueries
3. Part 3 - Tricks and Efficient Code
4. Part 4 - Subqueries in the From Clause
5. Part 5 - Mixed Bag of John's Tricks

Part 1 - Joins and Unions

This answer covers:

1. Part 1
   • Joining two or more tables using an inner join (See the wikipedia entry for additional info)
   • How to use a union query
   • Left and Right Outer Joins (this stackOverflow answer is excellent to describe types of joins)
   • Intersect queries (and how to reproduce them if your database doesn't support them) - this is a function of SQL-Server (see info) and part of the reason I wrote this whole thing in the first place.
2. Part 2
   • Subqueries - what they are, where they can be used and what to watch out for
   • Cartesian joins AKA - Oh, the misery!

There are a number of ways to retrieve data from multiple tables in a database. In this answer, I will be using ANSI-92 join syntax. This may be different to a number of other tutorials out there which use the older ANSI-89 syntax (and if you are used to 89, may seem much less intuitive - but all I can say is to try it) as it is much easier to understand when the queries start getting more complex. Why use it? Is there a performance gain? The short answer is no, but it is easier to read once you get used to it. It is easier to read queries written by other folks using this syntax.

I am also going to use the concept of a small caryard which has a database to keep track of what cars it has available. The owner has hired you as his IT Computer guy and expects you to be able to drop him the data that he asks for at the drop of a hat.

I have made a number of lookup tables that will be used by the final table. This will give us a reasonable model to work from. To start off, I will be running my queries against an example database that has the following structure. I will try to think of common mistakes that are made when starting out and explain what goes wrong with them - as well as of course showing how to correct them.

The first table is simply a color listing so that we know what colors we have in the car yard.

mysql> create table colors(id int(3) not null auto_increment primary key, 
    -> color varchar(15), paint varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| color | varchar(15) | YES  |     | NULL    |                |
| paint | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into colors (color, paint) values ('Red', 'Metallic'), 
    -> ('Green', 'Gloss'), ('Blue', 'Metallic'), 
    -> ('White' 'Gloss'), ('Black' 'Gloss');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from colors;
+----+-------+----------+
| id | color | paint    |
+----+-------+----------+
|  1 | Red   | Metallic |
|  2 | Green | Gloss    |
|  3 | Blue  | Metallic |
|  4 | White | Gloss    |
|  5 | Black | Gloss    |
+----+-------+----------+
5 rows in set (0.00 sec)

The brands table identifies the different brands of the cars out caryard could possibly sell.

mysql> create table brands (id int(3) not null auto_increment primary key, 
    -> brand varchar(15));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from brands;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| brand | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into brands (brand) values ('Ford'), ('Toyota'), 
    -> ('Nissan'), ('Smart'), ('BMW');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from brands;
+----+--------+
| id | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  3 | Nissan |
|  4 | Smart  |
|  5 | BMW    |
+----+--------+
5 rows in set (0.00 sec)

The model table will cover off different types of cars, it is going to be simpler for this to use different car types rather than actual car models.

mysql> create table models (id int(3) not null auto_increment primary key, 
    -> model varchar(15));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from models;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| model | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into models (model) values ('Sports'), ('Sedan'), ('4WD'), ('Luxury');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from models;
+----+--------+
| id | model  |
+----+--------+
|  1 | Sports |
|  2 | Sedan  |
|  3 | 4WD    |
|  4 | Luxury |
+----+--------+
4 rows in set (0.00 sec)

And finally, to tie up all these other tables, the table that ties everything together. The ID field is actually the unique lot number used to identify cars.

mysql> create table cars (id int(3) not null auto_increment primary key, 
    -> color int(3), brand int(3), model int(3));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from cars;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | int(3) | NO   | PRI | NULL    | auto_increment |
| color | int(3) | YES  |     | NULL    |                |
| brand | int(3) | YES  |     | NULL    |                |
| model | int(3) | YES  |     | NULL    |                |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1), 
    -> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from cars;
+----+-------+-------+-------+
| id | color | brand | model |
+----+-------+-------+-------+
|  1 |     1 |     2 |     1 |
|  2 |     3 |     1 |     2 |
|  3 |     5 |     3 |     1 |
|  4 |     4 |     4 |     2 |
|  5 |     2 |     2 |     3 |
|  6 |     3 |     5 |     4 |
|  7 |     4 |     1 |     3 |
|  8 |     2 |     2 |     1 |
|  9 |     5 |     2 |     3 |
| 10 |     4 |     5 |     1 |
+----+-------+-------+-------+
10 rows in set (0.00 sec)

This will give us enough data (I hope) to cover off the examples below of different types of joins and also give enough data to make them worthwhile.

So getting into the grit of it, the boss wants to know The IDs of all the sports cars he has.

This is a simple two table join. We have a table that identifies the model and the table with the available stock in it. As you can see, the data in the model column of the cars table relates to the models column of the cars table we have. Now, we know that the models table has an ID of 1 for Sports so lets write the join.

select
    ID,
    model
from
    cars
        join models
            on model=ID

So this query looks good right? We have identified the two tables and contain the information we need and use a join that correctly identifies what columns to join on.

ERROR 1052 (23000): Column 'ID' in field list is ambiguous

Oh noes! An error in our first query! Yes, and it is a plum. You see, the query has indeed got the right columns, but some of them exist in both tables, so the database gets confused about what actual column we mean and where. There are two solutions to solve this. The first is nice and simple, we can use tableName.columnName to tell the database exactly what we mean, like this:

select
    cars.ID,
    models.model
from
    cars
        join models
            on cars.model=models.ID

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
|  2 | Sedan  |
|  4 | Sedan  |
|  5 | 4WD    |
|  7 | 4WD    |
|  9 | 4WD    |
|  6 | Luxury |
+----+--------+
10 rows in set (0.00 sec)

The other is probably more often used and is called table aliasing. The tables in this example have nice and short simple names, but typing out something like KPI_DAILY_SALES_BY_DEPARTMENT would probably get old quickly, so a simple way is to nickname the table like this:

select
    a.ID,
    b.model
from
    cars a
        join models b
            on a.model=b.ID

Now, back to the request. As you can see we have the information we need, but we also have information that wasn't asked for, so we need to include a where clause in the statement to only get the Sports cars as was asked. As I prefer the table alias method rather than using the table names over and over, I will stick to it from this point onwards.

Clearly, we need to add a where clause to our query. We can identify Sports cars either by ID=1 or model='Sports'. As the ID is indexed and the primary key (and it happens to be less typing), lets use that in our query.

select
    a.ID,
    b.model
from
    cars a
        join models b
            on a.model=b.ID
where
    b.ID=1

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)

Bingo! The boss is happy. Of course, being a boss and never being happy with what he asked for, he looks at the information, then says I want the colors as well.

Okay, so we have a good part of our query already written, but we need to use a third table which is colors. Now, our main information table cars stores the car color ID and this links back to the colors ID column. So, in a similar manner to the original, we can join a third table:

select
    a.ID,
    b.model
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
where
    b.ID=1

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)

Damn, although the table was correctly joined and the related columns were linked, we forgot to pull in the actual information from the new table that we just linked.

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
where
    b.ID=1

+----+--------+-------+
| ID | model  | color |
+----+--------+-------+
|  1 | Sports | Red   |
|  8 | Sports | Green |
| 10 | Sports | White |
|  3 | Sports | Black |
+----+--------+-------+
4 rows in set (0.00 sec)

Right, that's the boss off our back for a moment. Now, to explain some of this in a little more detail. As you can see, the from clause in our statement links our main table (I often use a table that contains information rather than a lookup or dimension table. The query would work just as well with the tables all switched around, but make less sense when we come back to this query to read it in a few months time, so it is often best to try to write a query that will be nice and easy to understand - lay it out intuitively, use nice indenting so that everything is as clear as it can be. If you go on to teach others, try to instill these characteristics in their queries - especially if you will be troubleshooting them.

It is entirely possible to keep linking more and more tables in this manner.

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1

While I forgot to include a table where we might want to join more than one column in the join statement, here is an example. If the models table had brand-specific models and therefore also had a column called brand which linked back to the brands table on the ID field, it could be done as this:

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
            and b.brand=d.ID
where
    b.ID=1

You can see, the query above not only links the joined tables to the main cars table, but also specifies joins between the already joined tables. If this wasn't done, the result is called a cartesian join - which is dba speak for bad. A cartesian join is one where rows are returned because the information doesn't tell the database how to limit the results, so the query returns all the rows that fit the criteria.

So, to give an example of a cartesian join, lets run the following query:

select
    a.ID,
    b.model
from
    cars a
        join models b

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  1 | Sedan  |
|  1 | 4WD    |
|  1 | Luxury |
|  2 | Sports |
|  2 | Sedan  |
|  2 | 4WD    |
|  2 | Luxury |
|  3 | Sports |
|  3 | Sedan  |
|  3 | 4WD    |
|  3 | Luxury |
|  4 | Sports |
|  4 | Sedan  |
|  4 | 4WD    |
|  4 | Luxury |
|  5 | Sports |
|  5 | Sedan  |
|  5 | 4WD    |
|  5 | Luxury |
|  6 | Sports |
|  6 | Sedan  |
|  6 | 4WD    |
|  6 | Luxury |
|  7 | Sports |
|  7 | Sedan  |
|  7 | 4WD    |
|  7 | Luxury |
|  8 | Sports |
|  8 | Sedan  |
|  8 | 4WD    |
|  8 | Luxury |
|  9 | Sports |
|  9 | Sedan  |
|  9 | 4WD    |
|  9 | Luxury |
| 10 | Sports |
| 10 | Sedan  |
| 10 | 4WD    |
| 10 | Luxury |
+----+--------+
40 rows in set (0.00 sec)

Good god, that's ugly. However, as far as the database is concerned, it is exactly what was asked for. In the query, we asked for for the ID from cars and the model from models. However, because we didn't specify how to join the tables, the database has matched every row from the first table with every row from the second table.

Okay, so the boss is back, and he wants more information again. I want the same list, but also include 4WDs in it.

This however, gives us a great excuse to look at two different ways to accomplish this. We could add another condition to the where clause like this:

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1
    or b.ID=3

While the above will work perfectly well, lets look at it differently, this is a great excuse to show how a union query will work.

We know that the following will return all the Sports cars:

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1

And the following would return all the 4WDs:

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=3

So by adding a union all clause between them, the results of the second query will be appended to the results of the first query.

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1
union all
select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=3

+----+--------+-------+
| ID | model  | color |
+----+--------+-------+
|  1 | Sports | Red   |
|  8 | Sports | Green |
| 10 | Sports | White |
|  3 | Sports | Black |
|  5 | 4WD    | Green |
|  7 | 4WD    | White |
|  9 | 4WD    | Black |
+----+--------+-------+
7 rows in set (0.00 sec)

As you can see, the results of the first query are returned first, followed by the results of the second query.

In this example, it would of course have been much easier to simply use the first query, but union queries can be great for specific cases. They are a great way to return specific results from tables from tables that aren't easily joined together - or for that matter completely unrelated tables. There are a few rules to follow however.

• The column types from the first query must match the column types from every other query below.
• The names of the columns from the first query will be used to identify the entire set of results.
• The number of columns in each query must be the same.

Now, you might be wondering what the difference is between using union and union all. A union query will remove duplicates, while a union all will not. This does mean that there is a small performance hit when using union over union all but the results may be worth it - I won't speculate on that sort of thing in this though.

On this note, it might be worth noting some additional notes here.

• If we wanted to order the results, we can use an order by but you can't use the alias anymore. In the query above, appending an order by a.ID would result in an error - as far as the results are concerned, the column is called ID rather than a.ID - even though the same alias has been used in both queries.
• We can only have one order by statement, and it must be as the last statement.

For the next examples, I am adding a few extra rows to our tables.

I have added Holden to the brands table. I have also added a row into cars that has the color value of 12 - which has no reference in the colors table.

Okay, the boss is back again, barking requests out - *I want a count of each brand we carry and the number of cars in it!` - Typical, we just get to an interesting section of our discussion and the boss wants more work.

Rightyo, so the first thing we need to do is get a complete listing of possible brands.

select
    a.brand
from
    brands a

+--------+
| brand  |
+--------+
| Ford   |
| Toyota |
| Nissan |
| Smart  |
| BMW    |
| Holden |
+--------+
6 rows in set (0.00 sec)

Now, when we join this to our cars table we get the following result:

select
    a.brand
from
    brands a
        join cars b
            on a.ID=b.brand
group by
    a.brand

+--------+
| brand  |
+--------+
| BMW    |
| Ford   |
| Nissan |
| Smart  |
| Toyota |
+--------+
5 rows in set (0.00 sec)

Which is of course a problem - we aren't seeing any mention of the lovely Holden brand I added.

This is because a join looks for matching rows in both tables. As there is no data in cars that is of type Holden it isn't returned. This is where we can use an outer join. This will return all the results from one table whether they are matched in the other table or not:

select
    a.brand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
group by
    a.brand

+--------+
| brand  |
+--------+
| BMW    |
| Ford   |
| Holden |
| Nissan |
| Smart  |
| Toyota |
+--------+
6 rows in set (0.00 sec)

Now that we have that, we can add a lovely aggregate function to get a count and get the boss off our backs for a moment.

select
    a.brand,
    count(b.id) as countOfBrand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
group by
    a.brand

+--------+--------------+
| brand  | countOfBrand |
+--------+--------------+
| BMW    |            2 |
| Ford   |            2 |
| Holden |            0 |
| Nissan |            1 |
| Smart  |            1 |
| Toyota |            5 |
+--------+--------------+
6 rows in set (0.00 sec)

And with that, away the boss skulks.

Now, to explain this in some more detail, outer joins can be of the left or right type. The Left or Right defines which table is fully included. A left outer join will include all the rows from the table on the left, while (you guessed it) a right outer join brings all the results from the table on the right into the results.

Some databases will allow a full outer join which will bring back results (whether matched or not) from both tables, but this isn't supported in all databases.

Now, I probably figure at this point in time, you are wondering whether or not you can merge join types in a query - and the answer is yes, you absolutely can.

select
    b.brand,
    c.color,
    count(a.id) as countOfBrand
from
    cars a
        right outer join brands b
            on b.ID=a.brand
        join colors c
            on a.color=c.ID
group by
    a.brand,
    c.color

+--------+-------+--------------+
| brand  | color | countOfBrand |
+--------+-------+--------------+
| Ford   | Blue  |            1 |
| Ford   | White |            1 |
| Toyota | Black |            1 |
| Toyota | Green |            2 |
| Toyota | Red   |            1 |
| Nissan | Black |            1 |
| Smart  | White |            1 |
| BMW    | Blue  |            1 |
| BMW    | White |            1 |
+--------+-------+--------------+
9 rows in set (0.00 sec)

So, why is that not the results that were expected? It is because although we have selected the outer join from cars to brands, it wasn't specified in the join to colors - so that particular join will only bring back results that match in both tables.

Here is the query that would work to get the results that we expected:

select
    a.brand,
    c.color,
    count(b.id) as countOfBrand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
        left outer join colors c
            on b.color=c.ID
group by
    a.brand,
    c.color

+--------+-------+--------------+
| brand  | color | countOfBrand |
+--------+-------+--------------+
| BMW    | Blue  |            1 |
| BMW    | White |            1 |
| Ford   | Blue  |            1 |
| Ford   | White |            1 |
| Holden | NULL  |            0 |
| Nissan | Black |            1 |
| Smart  | White |            1 |
| Toyota | NULL  |            1 |
| Toyota | Black |            1 |
| Toyota | Green |            2 |
| Toyota | Red   |            1 |
+--------+-------+--------------+
11 rows in set (0.00 sec)

As we can see, we have two outer joins in the query and the results are coming through as expected.

Now, how about those other types of joins you ask? What about Intersections?

Well, not all databases support the intersection but pretty much all databases will allow you to create an intersection through a join (or a well structured where statement at the least).

An Intersection is a type of join somewhat similar to a union as described above - but the difference is that it only returns rows of data that are identical (and I do mean identical) between the various individual queries joined by the union. Only rows that are identical in every regard will be returned.

A simple example would be as such:

select
    *
from
    colors
where
    ID>2
intersect
select
    *
from
    colors
where
    id<4

While a normal union query would return all the rows of the table (the first query returning anything over ID>2 and the second anything having ID<4) which would result in a full set, an intersect query would only return the row matching id=3 as it meets both criteria.

Now, if your database doesn't support an intersect query, the above can be easily accomlished with the following query:

select
    a.ID,
    a.color,
    a.paint
from
    colors a
        join colors b
            on a.ID=b.ID
where
    a.ID>2
    and b.ID<4

+----+-------+----------+
| ID | color | paint    |
+----+-------+----------+
|  3 | Blue  | Metallic |
+----+-------+----------+
1 row in set (0.00 sec)

If you wish to perform an intersection across two different tables using a database that doesn't inherently support an intersection query, you will need to create a join on every column of the tables.

這篇關(guān)于SQL查詢從多個(gè)表返回?cái)?shù)據(jù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！