[FIXED] How to do LEFT JOIN a ON b = c, but if joined table is NULL, join on x instead in (Postgres) SQL?

Issue

Given I have two SQL tables:

movies          
----------------
| id | title   |
----------------
| 1  | Matrix  |
| 2  | Titanic |
----------------


directors
-----------------------
| id | name | movieid |
-----------------------
| 1  | Steve | 1      |
-----------------------

If I do a typical left join;

SELECT *
FROM movies
LEFT JOIN directors ON directors.movieid = movies.id

Then then output will be similar to:

-----------------------------------------
| id | title   | id   | name  | movieid |
-----------------------------------------
| 1  | Matrix  | 1    | Steve | 1       |
| 2  | Titanic | NULL | NULL  | NULL    |
-----------------------------------------

Because for movie 2 there is no director, as expected.

However, in the cases where the join results in a NULL match, I want to provide a default value:

In pseudo SQL:

SELECT *
FROM movies
LEFT JOIN directors ON directors.movieid = (movies.id || 1)

Meaning, if the join results in a NULL match, join on the (default) value 1 instead, which would return ideally for me:

-----------------------------------------
| id | title   | id   | name  | movieid |
-----------------------------------------
| 1  | Matrix  | 1    | Steve | 1       |
| 2  | Titanic | 1    | Steve | 1       |
-----------------------------------------

Meaning, I want to provide a default director for all movies, if the JOIN fails.

Is it possible to do something like this in (Postgres) SQL?

Solution

Since you need to work with additional joins and operations, you need to create a CTE for directors of each movie and then use that CTE table instead of the directors table.

WITH cte
AS (
 SELECT COALESCE(directors.id, d2.id) AS id
  ,COALESCE(directors.name, d2.name) AS name
  ,COALESCE(movies.id, d2.movieid) AS movieid
 FROM movies
 LEFT JOIN directors ON directors.movieid = movies.id
 LEFT JOIN directors d2 ON d2.movieid = 1
 )
SELECT *
FROM movies
LEFT JOIN cte ON cte.movieid = movies.id

Output with additional data:

id	title	id	name	movieid
1	Matrix	1	Steve	1
2	Titanic	1	Steve	2
3	Avatar	2	Mark	3
4	Mission Impossible	1	Steve	4

See this fiddle.

In case in movieid field you require it to match with directors' table, add another field in cte ,COALESCE(directors.movieid, d2.movieid) AS dmovieid and use that in SELECT statement (but don't use it in the join). E.g. here

Answered By - Himanshu

Answer Checked By - Gilberto Lyons (PHPFixing Admin)