SQL - 这些连接之间的区别？ [英] SQL - Difference between these Joins?

问题描述

现在我应该知道这一点，但是下面两个语句之间的区别是什么？

嵌套连接：

  SELECT 
 t1。* 
 FROM 
 table1 t1 
 INNER JOIN table2 t2 
 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID 
 ON t2.table2_ID = t1.table1_ID 
  

更传统的连接：

  SELECT 
 t1。* 
 FROM 
 table1 t1 
 INNER JOIN table2 t2 ON t2.table2_ID = t1.table1_ID 
 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID 
  

解决方案

$ b

  SELECT 
 t1。* 
 FROM 
 table1 t1 
 INNER JOIN table2 t2 
 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID 
 ON t2.table2_ID = t1.table1_ID 
  

可以重写为：

  SELECT 
 t1。* 
 FROM 
 table1 t1 -  inner join t1 
 INNER JOIN 
（table2 t2 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID） -  with this 
 ON t2.table2_ID = t1.table1_ID  -  on这个条件
  

所以基本上，首先你LEFT JOIN t2与t3，基于连接条件：table3_ID =

在你的第二个例子中，你先用INNER JOIN t1和t2，然后LEFT JOIN，得到的内部连接表t3上的条件table2_ID = table1_ID。

  SELECT 
 t1。* 
 FROM 
 table1 t1 
 INNER JOIN table2 t2 ON t2.table2_ID = t1.table1_ID 
 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID 
  

可以重写为：

  SELECT 
 t1。 
 FROM 
（table1 t1 INNER JOIN table2 t2 ON t2.table2_ID = t1.table1_ID） -  first inner join 
 LEFT JOIN  -  then left join 
 table3 t3 ON t3。 table3_ID = t2.table2_ID  - 此结果
  

EDIT p>

我道歉。我的第一句话是错误的。这两个查询将产生相同的结果，但是在性能上可能存在差异，因为在一些实例中（当表1仅包含表2中的元素的子集时）第一查询可能执行得比第二查询慢，因为LEFT JOIN将首先执行 - 然后与table1相交。与允许查询优化器执行其工作的第二个查询相反。

I should probably know this by now, but what, if any is the difference between the two statements below?

The nested join:

SELECT
    t1.*
FROM
    table1 t1
    INNER JOIN table2 t2
        LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID
    ON t2.table2_ID = t1.table1_ID

The more traditional join:

SELECT
    t1.*
FROM
    table1 t1
    INNER JOIN table2 t2 ON t2.table2_ID = t1.table1_ID
    LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID

解决方案

Well, it's the order of operations..

SELECT
    t1.*
FROM
    table1 t1
    INNER JOIN table2 t2
        LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID
    ON t2.table2_ID = t1.table1_ID

could be rewritten as:

SELECT
    t1.*
FROM
       table1 t1                                                       -- inner join t1
    INNER JOIN 
       (table2 t2 LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID)  -- with this 
    ON t2.table2_ID = t1.table1_ID                                     -- on this condition

So basically, first you LEFT JOIN t2 with t3, based on the join condition: table3_ID = table2_ID, then you INNER JOIN t1 with t2 on table2_ID = table1_ID.

In your second example you first INNER JOIN t1 with t2, and then LEFT JOIN the resulting inner join with table t3 on the condition table2_ID = table1_ID.

SELECT
    t1.*
FROM
    table1 t1
    INNER JOIN table2 t2 ON t2.table2_ID = t1.table1_ID
    LEFT JOIN table3 t3 ON t3.table3_ID = t2.table2_ID        

could be rewritten as:

SELECT
    t1.*
FROM
        (table1 t1 INNER JOIN table2 t2 ON t2.table2_ID = t1.table1_ID) -- first inner join
    LEFT JOIN                                                           -- then left join
        table3 t3 ON t3.table3_ID = t2.table2_ID                        -- the result with this

EDIT

I apologize. My first remark was wrong. The two queries will produce the same results but there may be a difference in performance as the first query may perform slower than the second query in some instances ( when table 1 contains only a subset of the elements in table 2) as the LEFT JOIN will be executed first - and only then intersected with table1. As opposed to the second query which allows the query optimizer to do it's job.

这篇关于SQL - 这些连接之间的区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！