SQL左连接独立地连接两个表 [英] SQL left join two tables independently

问题描述

如果我有这些表:

Thing
id | name
---+---------
1  | thing 1
2  | thing 2
3  | thing 3

Photos
id | thing_id | src
---+----------+---------
1  | 1        | thing-i1.jpg
2  | 1        | thing-i2.jpg
3  | 2        | thing2.jpg

Ratings
id | thing_id | rating
---+----------+---------
1  | 1        | 6
2  | 2        | 3
3  | 2        | 4

我如何加入他们的队伍

id | name    | rating | photo
---+---------+--------+--------
1  | thing 1 | 6      | NULL
1  | thing 1 | NULL   | thing-i1.jpg
1  | thing 1 | NULL   | thing-i2.jpg

2  | thing 2 | 3      | NULL
2  | thing 2 | 4      | NULL
2  | thing 2 | NULL   | thing2.jpg

3  | thing 3 | NULL   | NULL

是，同时在每个表上左联接，而不是在下一个表上左联接?

Ie, left join on each table simultaneously, rather than left joining on one than the next?

这是我能得到的最接近的

This is the closest I can get:

SELECT Thing.*, Rating.rating, Photo.src
From Thing
Left Join Photo on Thing.id = Photo.thing_id
Left Join Rating on Thing.id = Rating.thing_id

推荐答案

您可以通过并集获得所需的结果，这似乎是最明显的，因为您从排名或照片中返回了一个字段.

You can get the results you want with a union, which seems the most obvious, since you return a field from either ranking or photo.

您的其他情况(都不存在)是通过使联接成为左联接而不是内部联接来解决的.您将获得一条重复记录，其排名，照片中为 NULL，NULL .您可以通过将批次移至子查询并在主查询上进行选择不同来过滤掉，但是更明显的解决方案是用 union替换 union all ，它还会过滤出重复项.更简单易读.

Your additional case (have none of either), is solved by making the joins left join instead of inner joins. You will get a duplicate record with NULL, NULL in ranking, photo. You can filter this out by moving the lot to a subquery and do select distinct on the main query, but the more obvious solution is to replace union all by union, which also filters out duplicates. Easier and more readable.

select
  t.id,
  t.name,
  r.rating,
  null as photo
from
  Thing t
  left join Rating r on r.thing_id = t.id
union
select
  t.id,
  t.name,
  null,
  p.src
from
  Thing t
  left join Photo p on p.thing_id = t.id
order by
  id,
  photo,
  rating

这篇关于SQL左连接独立地连接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！