在 SQL 中连接多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15987321/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining multiple tables in SQL
提问by Prince
Can sombody Explains me about joins?
有人可以向我解释连接吗？
Inner join selects common data based on where condition.
内连接根据 where 条件选择公共数据。
Left outer join selects all data from left irrespective of common but takes common data from right table and vice versa for Right outer.
左外连接从左侧选择所有数据，而不考虑公共数据，但从右表中获取公共数据，反之亦然。
I know the basics but question stays when it comes to join for than 5, 8, 10 tables.
我知道基础知识，但在加入超过 5、8、10 个表时问题仍然存在。
Suppose I have 10 tables to join. If I have inner join with the first 5 tables and now try to apply a left join with the 6th table, now how the query will work?
假设我有 10 个表要加入。如果我对前 5 个表进行内连接，现在尝试对第 6 个表应用左连接，那么查询将如何工作？
I mean to say now the result set of first 5 tables will be taken as left table and the 6th one will be considerded as Right table? Or only Fifth table will be considered as left and 6th as right? Please help me regarding this.
我的意思是说，现在前 5 个表的结果集将被视为左表，第 6 个将被视为右表？或者只有第五张桌子会被认为是左边，第六张是右边？请帮我解决这个问题。
回答by Martin Smith
When joining multiple tables the output of each join logically forms a virtual table that goes into the next join.
当连接多个表时，每个连接的输出在逻辑上形成一个进入下一个连接的虚拟表。
So in the example in your question the composite result of joining the first 5 tables would be treated as the left hand table.
因此，在您问题中的示例中，加入前 5 个表的复合结果将被视为左侧表。
See Itzik Ben-Gan's Logical Query Processing Posterfor more about this.
有关这方面的更多信息，请参阅Itzik Ben-Gan 的逻辑查询处理海报。
The virtual tables involved in the joins can be controlled by positioning the ONclause. For example
连接中涉及的虚拟表可以通过定位ON子句来控制。例如
SELECT *
FROM T1
INNER JOIN T2
ON T2.C = T1.C
INNER JOIN T3
LEFT JOIN T4
ON T4.C = T3.C
ON T3.C = T2.C
is equivalent to (T1 Inner Join T2) Inner Join (T3 Left Join T4)
相当于 (T1 Inner Join T2) Inner Join (T3 Left Join T4)
回答by Colselaw
It's helpful to think of JOIN's in sequence, so the former is correct.
JOIN按顺序考虑's是有帮助的，所以前者是正确的。
SELECT *
FROM a
INNER JOIN b ON b.a = a.id
INNER JOIN c ON c.b = b.id
LEFT JOIN d ON d.c = c.id
LEFT JOIN e ON e.d = d.id
Would be all the fields from aand band cwhere all the ONcriteria match, plus the values from dwhere its criteria match plus all the contents of ewhere all its criteria match.
将来自各个领域a，并b与c所有的ON标准匹配，加上从值d的标准相匹配，其中加的所有内容e，其中所有的标准相匹配。
I know RIGHT JOINis perfectly acceptable, but I've found in my experience that it's unnecessary - I almost always just join things from left to right.
我知道这RIGHT JOIN是完全可以接受的，但我发现根据我的经验，这是不必要的 - 我几乎总是从左到右加入。
回答by Manu R S
> Simple INNER JOIN VIEW code...
> 简单的 INNER JOIN VIEW 代码...
CREATE VIEW room_view
AS SELECT a.*,b.*
FROM j4_booking a INNER JOIN j4_scheduling b
on a.room_id = b.room_id;
回答by Bha15
You can apply join like this..
您可以像这样申请加入..
select a.*,b.*,c.*,d.*,e.*
from [DatabaseName].[Table_a] a
INNER JOIN [DatabaseName].[Table_b] b ON a.id = b.id
INNER JOIN [DatabaseName].[Table_c] c ON b.id=c.id
INNER JOIN [DatabaseName].[Table_d] d on c.id=d.id
INNER JOIN [DatabaseName].[Table_e] e on d.id=e.id where a.con=5 and
b.con=6
Here, at place of a.* and in where condition, you can show column(filed) which you like and according condition in where condition. You can insert more table and database as per your choice. But mind that you need to mention database name and alias if you work in different database.
在这里，在 a.* 的位置和 where 条件下，您可以在 where 条件下显示您喜欢的列（归档）和根据条件。您可以根据自己的选择插入更多表和数据库。但请注意，如果您在不同的数据库中工作，则需要提及数据库名称和别名。
