我有三個表：
mysql> SELECT * FROM 相冊 LIMIT 3;
----- ------------------------- ----------- -------------- --------------
| id | name | artist_id | release_date | release_year |
----- ------------------------- ----------- -------------- --------------
| 4 | Blue Lines | 4 | 1991-04-08 | 1991 |
| 335 | Madman Across the Water | 236 | 1971-11-05 | 1971 |
| 436 | The Singles 81>85 | 317 | 1985-10-15 | 1985 |
----- ------------------------- ----------- -------------- --------------
mysql> SELECT * FROM 曲目限制 3;
---- ---------------- ---------- ---------- ----------
| id | name | position | length | album_id |
---- ---------------- ---------- ---------- ----------
| 50 | Safe From Harm | 1 | 00:05:19 | 4 |
| 51 | One Love | 2 | 00:04:49 | 4 |
| 52 | Blue Lines | 3 | 00:04:22 | 4 |
---- ---------------- ---------- ---------- ----------
mysql> SELECT * FROM 藝術家 LIMIT 3;
---- ---------------- ------------ ---------- ---------------- -------- --------
| id | name | start_year | end_year | origin | type | gender |
---- ---------------- ------------ ---------- ---------------- -------- --------
| 4 | Massive Attack | 1987 | NULL | United Kingdom | Group | NULL |
| 17 | Bob Dylan | 1941 | NULL | United States | Person | Male |
| 20 | Art of Noise | 1983 | 2000 | United Kingdom | Group | NULL |
---- ---------------- ------------ ---------- ---------------- -------- --------
所以從這些表中我想選擇在資料庫中沒有列出專輯的藝術家。我試過這個命令：SELECT Artist.name，albums.name FROM Artists，albums WHERE albums.name IS NULL; 但它沒有用
uj5u.com熱心網友回復：
你應該left join與Album表像
select a.* from Artist a
left join Album al on a.id = al.artist_id
where al.name is null;
uj5u.com熱心網友回復：
SELECT *
FROM artists
WHERE NOT EXISTS ( SELECT NULL
FROM albums
WHERE artists.id = albums.artist_id );
uj5u.com熱心網友回復：
您可以使用not in如下所示：
select * from artists a where a.id
not in (SELECT artist_id FROM albums)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/377352.html