在 SQL 中连接多个表

2021-12-17 00:00:00 join sql sql-server

有人能解释一下加入吗?

Can sombody Explains me about joins?

内连接根据 where 条件选择常用数据.

Inner join selects common data based on where condition.

左外连接从左选择所有数据而不管公共数据,但从右表中获取公共数据,反之亦然.

Left outer join selects all data from left irrespective of common but takes common data from right table and vice versa for Right outer.

我知道基础知识,但在加入超过 5、8、10 个表时问题仍然存在.

I know the basics but question stays when it comes to join for than 5, 8, 10 tables.

假设我有 10 个表要加入.如果我对前 5 个表进行内连接,现在尝试对第 6 个表应用左连接,那么查询将如何工作?

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?

我的意思是说现在前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.

推荐答案

当连接多个表时,每个连接的输出在逻辑上形成一个进入下一个连接的虚拟表.

When joining multiple tables the output of each join logically forms a virtual table that goes into the next join.

因此,在您问题的示例中,加入前 5 个表的复合结果将被视为左侧表.

So in the example in your question the composite result of joining the first 5 tables would be treated as the left hand table.

查看 Itzik Ben-Gan 的逻辑查询处理海报了解更多信息对这个.

See Itzik Ben-Gan's Logical Query Processing Poster for more about this.

连接中涉及的虚拟表可以通过定位ON 子句来控制.例如

The virtual tables involved in the joins can be controlled by positioning the ON clause. For example

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 

等价于(T1 Inner Join T2) Inner Join (T3 Left Join T4)

相关文章