mysql 左连接查询

发布于 2024-10-17 19:54:39 字数 330 浏览 0 评论 0原文

我对下面的代码片段有疑问。我想知道，如果第一个查询没有带来任何结果，我还会得到第二个查询的结果吗？

select *
from
(
  -- first query
) as query1
left join
(
  -- second query
) as query2
on query1.id=query2.id
left join
(
  -- third query
) as query3
on query1.id=query3.id;

更新：

我需要的是完全连接，但是MySQL不支持它，什么是模拟这个的好方法？

原文

I have a question about the snippet below. I'm wondering, if the first query doesn't bring any results, would I still get the results for the second query?

select *
from
(
  -- first query
) as query1
left join
(
  -- second query
) as query2
on query1.id=query2.id
left join
(
  -- third query
) as query3
on query1.id=query3.id;

Update:

what I need is a full join, however, MySQL does not support it, what would be a good way to emulate this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

听闻余生 2024-10-24 19:54:39

答案是否定的。

左连接将从左侧提取所有结果，并且仅提取右侧匹配的结果。右连接则相反。完整外部联接将从任一结果中提取所有结果，并仅联接那些可以联接的结果。

请注意，对于当前的数据库，明智的做法是避免完全外连接，因为它们的效率比其他连接低得多。我个人已经成功地用这样的技巧击败了 Oracle 和 PostgreSQL 中的完全外连接，这些技巧也可以用于模拟没有它们的数据库中的完全外连接：

SELECT some_id
  , MAX(foo) AS foo
  , MAX(bar) AS bar
  , MAX(baz) AS baz
  , MAX(blat) AS blat
FROM (
      SELECT A.some_id
        , A.foo
        , A.bar
        , NULL as baz
        , NULL as blat
      FROM A
    UNION ALL
      SELECT B.some_id
        , NULL as foo
        , NULL as bar
        , B.baz
        , B.blat
      FROM B
  ) AS subquery
GROUP BY some_id;

有可能这个查询不会按原样工作是因为数据库无法确定空列的类型。有一些解决方法。最简单的方法是创建一个具有显式列类型的临时表，使用 2 个插入查询模拟 UNION ALL，然后从临时表中进行查询。

（参见http://bentilly.blogspot.com/2011/02/sql -formatting-style.html 来解释我的格式化风格。）

The answer is no.

A left join will pull all results from the left, and only those results on the right that match. A right join does the reverse. A full outer join will pull all results from either and join just those that can be joined.

Note that with current databases it is wise to avoid full outer joins because they are much less efficient than other joins. I have personally managed to beat full outer joins in Oracle and PostgreSQL by orders of magnitude with tricks like this, which can also be used to emulate full outer joins in databases that don't have them:

SELECT some_id
  , MAX(foo) AS foo
  , MAX(bar) AS bar
  , MAX(baz) AS baz
  , MAX(blat) AS blat
FROM (
      SELECT A.some_id
        , A.foo
        , A.bar
        , NULL as baz
        , NULL as blat
      FROM A
    UNION ALL
      SELECT B.some_id
        , NULL as foo
        , NULL as bar
        , B.baz
        , B.blat
      FROM B
  ) AS subquery
GROUP BY some_id;

There is a possibility that this query will not work as is because the database won't figure out the types of the null columns. There are workarounds for that. The simplest is to create a temp table with explicit column types, emulate the UNION ALL with 2 insert queries, then query off of the temp table.

(See http://bentilly.blogspot.com/2011/02/sql-formatting-style.html for an explanation of my formatting style.)

回复收藏 0 原文