MySQL条件左连接？

Question

我有三个表： albums、songs 和 images。 这些表看起来像这样： albums 表：

id   |     title     |   image_id
5    |  First Album  |      1
6    | Another Album |      2

歌曲表：

songs

id    |   album_id   |     title     |   image_id
32    |      5       |    My Song    |      3
33    |      5       |  Another One  |      4
34    |      5       |   First Song  |      0
35    |      6       |   My Song #2  |      0
36    |      6       |  Fancy Title  |      0
37    |      6       |  My Love Song |      5

图片表：

images

id   |     path
1    | path/to/image1.jpg
2    | path/to/image2.jpg
3    | path/to/image3.jpg
4    | path/to/image4.jpg
5    | path/to/image5.jpg

我正在尝试显示一组歌曲及其标题和相应的图像。

如果歌曲没有图像（如果`image_id`列为`0`），那么我想只使用专辑图像。

目前我只有这段代码，我卡住了：

SELECT songs.title, images.path
FROM songs
LEFT JOIN images
ON images.id = songs.image_id

// ...if songs.image_id is 0, i want to just use the image_id of the corresponding album instead

如果它是一个能够处理大表格的高效查询，那就太好了。

- threedollarbill

---

使用COALESCE()函数。 - PM 77-1

从性能角度来看，使用NULL可能比使用0更好。这样，数据库在尝试JOIN之前就知道可能没有匹配的行。 - Thilo

Answer 1

类似以下代码应该可以工作：

SELECT s.title, COALESCE(si.path, ai.path)
FROM albums AS a
INNER JOIN songs s ON a.id = s.album_id
LEFT JOIN images ai ON i.id = a.image_id
LEFT JOIN images si ON i.id = s.image_id

请注意，您需要将images表连接到albums表和songs表。 ai和si用作这些连接的独立命名空间，以便COALESCE知道如何查找选择的选项。

（请注意，INNER JOIN在数据呈现方式方面存在争议。）

- Nathaniel Ford

---

最好将歌曲放在顶部。否则，没有专辑的歌曲将不会返回。 - Thilo

@Thilo 发现得好。正在修复！ - Nathaniel Ford

COALESCE不够用，因为对于缺失的图像，它具有0而不是NULL。但是处理这个问题的调整并不重要。 - mdahlman

@mdahlman，“si.path”应该为“NULL”（因为连接不会找到行），这一事实就足够了，对吧？（我目前没有可用的数据库可以尝试此操作。） - Nathaniel Ford

@NathanielFord，是的，你说得对。我的错误。但是COALESCE（s.path，ai.path）应该是COALESCE（si.path，ai.path）。 - mdahlman

回到“条件左连接部分”，可以这样说：LEFT JOIN images ai ON ai.id = a.image_id AND s.image_id = 0。 - Thilo

Answer 2

以下是使用多个LEFT JOIN的解决方案，同时提供了您提供的数据的演示。

最终查询语句如下：

SELECT 
    s.title, 
    COALESCE(i1.path, i2.path)
FROM songs s
LEFT JOIN albums a ON s.album_id = a.id
LEFT JOIN images i1 ON s.image_id = i1.id
LEFT JOIN images i2 ON a.image_id = i2.id
ORDER BY s.id;

下面是完整演示。

SQL：

-- Data for demo
create table albums(id int, title char(100), image_id int);
insert into albums values
(5    ,  'First Album'  ,      1),
(6    , 'Another Album' ,      2);

create table songs(id int, album_id int, title char(100), image_id int);
insert into songs values
(32    ,      5       ,    'My Song'    ,      3),
(33    ,      5       ,  'Another One'  ,      4),
(34    ,      5       ,   'First Song'  ,      0),
(35    ,      6       ,   'My Song #2'  ,      0),
(36    ,      6       ,  'Fancy Title'  ,      0),
(37    ,      6       ,  'My Love Song' ,      5);

create table images(id int, path char(200));
insert into images values
(1    , 'path/to/image1.jpg'),
(2    , 'path/to/image2.jpg'),
(3    , 'path/to/image3.jpg'),
(4    , 'path/to/image4.jpg'),
(5    , 'path/to/image5.jpg');

SELECT * FROM albums;
SELECT * FROM songs;
SELECT * FROM images;

-- SQL needed 
SELECT 
    s.title, 
    COALESCE(i1.path, i2.path)
FROM songs s
LEFT JOIN albums a ON s.album_id = a.id
LEFT JOIN images i1 ON s.image_id = i1.id
LEFT JOIN images i2 ON a.image_id = i2.id
ORDER BY s.id;

输出：

mysql> SELECT
    -> s.title,
    -> COALESCE(i1.path, i2.path)
    -> FROM songs s
    -> LEFT JOIN albums a ON s.album_id = a.id
    -> LEFT JOIN images i1 ON s.image_id = i1.id
    -> LEFT JOIN images i2 ON a.image_id = i2.id
    -> ORDER BY s.id;
+--------------+----------------------------+
| title        | COALESCE(i1.path, i2.path) |
+--------------+----------------------------+
| My Song      | path/to/image3.jpg         |
| Another One  | path/to/image4.jpg         |
| First Song   | path/to/image1.jpg         |
| My Song #2   | path/to/image2.jpg         |
| Fancy Title  | path/to/image2.jpg         |
| My Love Song | path/to/image5.jpg         |
+--------------+----------------------------+
6 rows in set (0.00 sec)

- Dylan Su

Answer 3

您也可以这样使用 UNION：

SELECT songs.title, images.path
FROM songs
LEFT JOIN images ON (images.id = songs.image_id)
WHERE songs.image_id != 0
UNION
SELECT songs.title, images.path
FROM songs
LEFT JOIN albums ON (songs.album_id = albums.id)
LEFT JOIN images ON (images.id = albums.image_id)
WHERE albums.image_id != 0 AND songs.image_id = 0

基本上，您首先选择所有带有图像的歌曲，然后将所有没有专辑图片的歌曲附加到带有专辑图片的歌曲。

- Ema