SQL 在同一个查询中使用左联接和内联接

Using left join and inner join in the same query

提问by Will

Below is my query using a left join that works as expected. What I want to do is add another table filter this query ever further but having trouble doing so. I will call this new table table_3and want to add where table_3.rwykey = runways_updatable.rwykey. Any help would be very much appreciated.

下面是我使用按预期工作的左连接的查询。我想要做的是进一步添加另一个表过滤器这个查询，但这样做有困难。我将调用这个新表table_3并想添加 where table_3.rwykey = runways_updatable.rwykey。任何帮助将不胜感激。

SELECT * 
FROM RUNWAYS_UPDATABLE 
LEFT JOIN TURN_UPDATABLE 
ON RUNWAYS_UPDATABLE.RWYKEY = TURN_UPDATABLE.RWYKEY 
WHERE RUNWAYS_UPDATABLE.ICAO = 'ICAO'
AND (RUNWAYS_UPDATABLE.TORA > 4000 OR LDA > 0) 
AND (TURN_UPDATABLE.AIRLINE_CODE IS NULL OR TURN_UPDATABLE.AIRLINE_CODE = '' 
OR TURN_UPDATABLE.AIRLINE_CODE = '') 

'*************EDIT To CLARIFY *****************Here is the other statement that inner join i would like to use and I would like to combine these 2 statements.

' *** *** *** ****编辑以澄清** *** *** *** *** ***这是我想使用的另一个内部连接语句，我想组合这两个语句。

SELECT * 
FROM RUNWAYS_UPDATABLE A, RUNWAYS_TABLE B
WHERE A.RWYKEY = B.RWYKEY

'***What I have so far as advice taken below, but getting syntax error

' *** 到目前为止，我有以下建议，但出现语法错误

     SELECT RUNWAYS_UPDATABLE.*, TURN_UPDATABLE.*,  AIRPORT_RUNWAYS_SELECTED.* 
     FROM RUNWAYS_UPDATABLE
       INNER JOIN  AIRPORT_RUNWAYS_SELECTED 
          ON RUNWAYS_UPDATABLE.RWYKEY = AIRPORT_RUNWAYS_SELECTED.RWYKEY
     LEFT JOIN TURN_UPDATABLE
          ON RUNWAYS_UPDATABLE.RWYKEY = TURN_UPDATABLE.RWYKEY 

NOTE: If i comment out the inner join and leave the left join or vice versa, it works but when I have both of joins in the query, thats when im getting the syntax error.

注意：如果我注释掉内部连接并保留左连接，反之亦然，它可以工作，但是当我在查询中同时拥有两个连接时，那就是我收到语法错误的时候。

回答by Nikola Markovinovi?

Remember that filtering a right-side table in left join should be done in join itself.

请记住，过滤左联接中的右侧表应该在联接本身中完成。

select *
from table1 
  left join table2
    on table1.FK_table2 = table2.id
    and table2.class = 'HIGH'

回答by Gajus

I always come across this question when searching for how to make LEFT JOINdepend on a further INNER JOIN. Here is an example for what I am searching when I am searching for "using LEFT JOINand INNER JOINin the same query":

在寻找如何使LEFT JOIN依赖进一步时，我总是遇到这个问题INNER JOIN。这是我在搜索“使用LEFT JOIN并INNER JOIN在同一查询中”时搜索的示例：

SELECT *
FROM foo f1
LEFT JOIN (bar b1
  INNER JOIN baz b2 ON b2.id = b1.baz_id
) ON
  b1.id = f1.bar_id

In this example, b1will only be included if b2is also found.

在此示例中，b1仅当b2还找到时才会包含在内。

回答by Will

I finally figured it out. Thanks for all your help!!!

我终于弄明白了。感谢你的帮助！！！

SELECT * FROM 
(AIRPORT_RUNWAYS_SELECTED 
 INNER JOIN RUNWAYS_UPDATABLE 
 ON AIRPORT_RUNWAYS_SELECTED.RWYKEY = RUNWAYS_UPDATABLE.RWYKEY) 
LEFT JOIN TURN_UPDATABLE ON RUNWAYS_UPDATABLE.RWYKEY = TURN_UPDATABLE.RWYKEY

回答by Travesty3

Add your INNER_JOINbefore your LEFT JOIN:

INNER_JOIN在您的之前添加您的LEFT JOIN：

  SELECT * 
  FROM runways_updatable ru
    INNER JOIN table_3 t3 ON ru.rwykey = t3.rwykey
    LEFT JOIN turn_updatable tu
      ON ru.rwykey = tu.rwykey
      AND (tu.airline_code IS NULL OR tu.airline_code = '' OR tu.airline_code = '')
  WHERE ru.icao = 'ICAO'
    AND (ru.tora > 4000 OR ru.lda > 0)

If you LEFT JOINbefore your INNER JOIN, then you will not get results from table_3if there is no matching row in turn_updatable. It's possible this is what you want, but since your join condition for table_3only references runways_updatable, I would assume that you want a result from table_3, even if there isn't a matching row in turn_updatable.

如果你LEFT JOIN在你之前INNER JOIN，那么table_3如果没有匹配的行，你将不会得到结果turn_updatable。这可能是您想要的，但由于您的连接条件table_3仅适用于 references runways_updatable，我会假设您想要来自 的结果table_3，即使 中没有匹配的行turn_updatable。

EDIT:

编辑：

As @NikolaMarkovinovi? pointed out, you should filter your LEFT JOINin the join condition itself, as you see above. Otherwise, you will not get results from the left-side table (runways_updatable) if that condition isn't met in the right-side table (turn_updatable).

作为@NikolaMarkovinovi？指出，您应该LEFT JOIN在连接条件本身中过滤您的内容，如上所示。否则，runways_updatable如果右侧表 ( ) 中不满足该条件，您将无法从左侧表 ( ) 中获得结果turn_updatable。

---

---

EDIT 2: OP mentioned this is actually Access, and not MySQL

编辑 2：OP 提到这实际上是 Access，而不是 MySQL

In Access, perhaps it's a difference in the table aliases. Try this instead:

在 Access 中，可能是表别名不同。试试这个：

  SELECT [ru].*, [tu].*, [ars].*
  FROM [runways_updatable] AS [ru]
    INNER JOIN [airport_runways_selected] AS [ars] ON [ru].rwykey = [ars].rwykey
    LEFT JOIN [turn_updatable] AS [tu]
      ON [ru].rwykey = [tu].rwykey
      AND ([tu].airline_code IS NULL OR [tu].airline_code = '' OR [tu].airline_code = '')
  WHERE [ru].icao = 'ICAO'
    AND ([ru].tora > 4000 OR [ru].lda > 0)

回答by Arion

I am not really sure what you want. But maybe something like this:

我不太确定你想要什么。但也许是这样的：

SELECT RUNWAYS_UPDATABLE.*, TURN_UPDATABLE.*
FROM RUNWAYS_UPDATABLE
JOIN table_3 
    ON table_3.rwykey = runways_updatable.rwykey
LEFT JOIN TURN_UPDATABLE 
ON RUNWAYS_UPDATABLE.RWYKEY = TURN_UPDATABLE.RWYKEY 
WHERE RUNWAYS_UPDATABLE.ICAO = 'ICAO'
AND (RUNWAYS_UPDATABLE.TORA > 4000 OR LDA > 0) 
AND (TURN_UPDATABLE.AIRLINE_CODE IS NULL OR TURN_UPDATABLE.AIRLINE_CODE = '' 
OR TURN_UPDATABLE.AIRLINE_CODE = '') 

回答by Justin Pihony

If it is just an inner join that you want to add, then do this. You can add as many joins as you want in the same query. Please update your answer if this is not what you want, though

如果它只是您要添加的内部联接，请执行此操作。您可以在同一查询中添加任意数量的联接。不过，如果这不是您想要的，请更新您的答案

  SELECT * 
  FROM RUNWAYS_UPDATABLE 
  LEFT JOIN TURN_UPDATABLE 
  ON RUNWAYS_UPDATABLE.RWYKEY = TURN_UPDATABLE.RWYKEY 
  INNER JOIN table_3
  ON table_3.rwykey = runways_updatable.rwykey
  WHERE RUNWAYS_UPDATABLE.ICAO = 'ICAO'
  AND (RUNWAYS_UPDATABLE.TORA > 4000 OR LDA > 0) 
  AND (TURN_UPDATABLE.AIRLINE_CODE IS NULL OR TURN_UPDATABLE.AIRLINE_CODE = '' 
  OR TURN_UPDATABLE.AIRLINE_CODE = '') 

回答by Debasish Mitra

For Postgres, query planner does not guarantee order of execution of join. To Guarantee one can use @Gajus solution but the problem arises if there are Where condition for inner join table's column(s). Either one would to require to carefully add the where clauses in the respective Join condition or otherwise it is better to use subquery the inner join part, and left join the output.

对于 Postgres，查询规划器不保证 join 的执行顺序。为了保证可以使用@Gajus 解决方案，但如果内连接表的列存在 Where 条件，则会出现问题。要么需要在相应的 Join 条件中仔细添加 where 子句，否则最好使用子查询内部连接部分，并 left join 输出。