使用子选择来完成LEFT JOIN

发布于 2024-08-25 23:49:07 字数 261 浏览 10 评论 0原文

在需要多列的情况下，可以通过子选择完成与 LEFT JOIN 等效的操作。这就是我的意思。

SELECT m.*, (SELECT * FROM model WHERE id = m.id LIMIT 1) AS models FROM make m

就目前情况而言，这样做会给我一个“操作数应包含 1 列”错误。

是的，我知道这可以通过 LEFT JOIN 实现，但有人告诉我可以通过子选择实现，我很好奇它是如何完成的。

原文

Is is possible to accomplish the equivalent of a LEFT JOIN with subselect where multiple columns are required.
Here's what I mean.

SELECT m.*, (SELECT * FROM model WHERE id = m.id LIMIT 1) AS models FROM make m

As it stands now doing this gives me a 'Operand should contain 1 column(s)' error.

Yes I know this is possible with LEFT JOIN, but I was told it was possible with subselect to I'm curious as to how it's done.

分享到QQ

分享到微博

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

发布评论

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

记忆之渊 2024-09-01 23:49:07

您的建议有很多实际用途。

对于至少有一个 release_date 的任何品牌，此假设查询将返回最新的 release_date（人为示例），对于任何没有 release_date 的品牌，返回 null >：

SELECT m.make_name, 
       sub.max_release_date
  FROM make m
       LEFT JOIN 
           (SELECT id, 
                   max(release_date) as max_release_date
              FROM make 
           GROUP BY 1) sub
       ON sub.id = m.id

There are many practical uses for what you suggest.

This hypothetical query would return the most recent release_date (contrived example) for any make with at least one release_date, and null for any make with no release_date:

SELECT m.make_name, 
       sub.max_release_date
  FROM make m
       LEFT JOIN 
           (SELECT id, 
                   max(release_date) as max_release_date
              FROM make 
           GROUP BY 1) sub
       ON sub.id = m.id

回复收藏 0 原文

⊕婉儿 2024-09-01 23:49:07

子选择只能返回一列，因此您需要为要从模型表返回的每一列提供一个子选择。

回复收藏 0 原文

半世蒼涼 2024-09-01 23:49:07

横向联接（又名在 SQL Server 中应用）对于希望从子查询中获取多列的人来说可能会有所帮助，特别是在您需要执行普通连接无法处理的事情的情况下（例如使用LIMIT）。

这是一个 PostgreSQL 示例：

with

make(id, make_name) as (
    values
        (1, 'Toyota'),
        (2, 'Ford'),
        (3, 'Chevrolet')
),

model(id, model_name, price, release_date) as (
    values
        (1, 'Corolla',   10000, '2000-01-01'),
        (1, 'Corolla',   15000, '2010-01-01'),
        (1, 'Camry',     20000, '2020-01-01'),
        (2, 'F-150',     25000, '2000-01-01'),
        (2, 'F-150',     30000, '2015-01-01'),
        (3, 'Silverado', 30000, '2000-01-01')
)

select
    make.id,
    make.make_name,
    models.model_name,
    models.price as current_price
from make
    left join lateral (
        select *
        from model
        where make.id = model.id
        order by release_date desc
        limit 1
    ) as models on 1=1
;

还有其他方法可以获得相同的输出，例如在加入模型之前过滤模型表。

Lateral joins (AKA apply in SQL Server) might be helpful for people looking to get multiple columns out of a subquery, particularly for cases where you need to do things that a normal join can't handle (like using LIMIT).

Here's a PostgreSQL example:

with

make(id, make_name) as (
    values
        (1, 'Toyota'),
        (2, 'Ford'),
        (3, 'Chevrolet')
),

model(id, model_name, price, release_date) as (
    values
        (1, 'Corolla',   10000, '2000-01-01'),
        (1, 'Corolla',   15000, '2010-01-01'),
        (1, 'Camry',     20000, '2020-01-01'),
        (2, 'F-150',     25000, '2000-01-01'),
        (2, 'F-150',     30000, '2015-01-01'),
        (3, 'Silverado', 30000, '2000-01-01')
)

select
    make.id,
    make.make_name,
    models.model_name,
    models.price as current_price
from make
    left join lateral (
        select *
        from model
        where make.id = model.id
        order by release_date desc
        limit 1
    ) as models on 1=1
;

There are other ways to get the same output, like filtering the model table before joining it.

回复收藏 0 原文

~没有更多了~