Types of Joins in SQL and what a particular instance will fall into

[Admin]

Fig 1 – Source: https://www.w3schools.com/sql/sql_join.asp

Here is the problem that I am solving:

All of the questions in this quiz refer to the open source Chinook Database. Please familiarize yourself with the ER diagram in order to familiarize yourself with the table and column names in order to write accurate queries and get the appropriate answers.

Find the name and ID of the artists who do not have albums.

The albums table seems subset of artists table in the sense that artistsId included in albums table will be included in the artists table. At the same time, there will be artists not having any albums.

What kind of JOIN will the above two tables will have relationship into?

The applicable case I could see is the RIGHT JOIN (fig 1), but the result should not display the green part (table2 ), instead white part (table1).

---

Reply

---

Here’s a tip: https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/.

Here’s another tip: never use RIGHT joins, always use LEFT joins instead.

the result should not display the green part (table2 ), instead white part (table1).

you’re looking for an anti-join

This is the code that might work here:

SELECT Artists.ArtistId, Artists.Name, Albums.Title
FROM Artists
LEFT JOIN Albums
ON Artists.ArtistId = Albums.ArtistId
WHERE Albums.Title IS NULL

Why selecting albums.title – you know that one’s null for any records selected. So why have it at all?

I included that for my own confirmation that getting null under Title. Yes, should be removed after that.

Depending on the size of the dataset, one may have just done a where condition subquery for not in the album table to avoid a join. Again, just depends on data scale plus where your indices exist.

SELECT Name, Artists.ArtistId
FROM Artists
WHERE NOT EXISTS
(SELECT ArtistId
FROM Albums
WHERE artists.artistid = albums.artistid)

[Author]

Posts