How to select specific data from tables in sql-CodePudding

I have three tables:

mysql> SELECT * FROM albums 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 tracks LIMIT 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 artists 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   |
 ---- ---------------- ------------ ---------- ---------------- -------- --------

So from these tables i want to select artists which dont have an album listed in the database. I tried this command: SELECT artists.name, albums.name FROM artists, albums WHERE albums.name IS NULL; but it didnt work

CodePudding user response：

You should left join with Album table like

select a.* from Artist a
left join Album al on a.id = al.artist_id
where al.name is null;

CodePudding user response：

SELECT *
FROM artists
WHERE NOT EXISTS ( SELECT NULL
                   FROM albums
                   WHERE artists.id = albums.artist_id );

CodePudding user response：

you can use not in as shown below :

select * from artists a where a.id 
not in (SELECT artist_id FROM albums)