Given I have two SQL tables:

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

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?

CodePudding user response：

WITH m AS(
SELECT movies.id, movies.tittle
FROM movies
LEFT JOIN directors
ON movies.id = directors.movieiD
WHERE directors.movieiD IS NULL
), 
d AS (
SELECT * FROM directors WHERE directors.id = 1
)

SELECT m.*, d.* FROM m, d

UNION

SELECT movies.id, movies.tittle, directors.id, directors.name, directors.movieid 
FROM movies
LEFT JOIN directors
ON movies.id = directors.movieiD
WHERE directors.movieiD IS NOT NULL

fiddle: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=3b90b49d07a6c50c2638d2c0fc66d72c

CodePudding user response：

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:

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

CodePudding user response：

You can write two JOIN, one joining existing entries and one joining the others with the movieid as 1 and then use COALESCE to replace NULL values by the values of your "fallback":

SELECT m.id, m.title, 
COALESCE(d.id, d2.id) AS directorId, 
COALESCE(d.name, d2.name) AS name,
COALESCE(d.movieid, d2.movieid) AS movieId
FROM movies m
LEFT JOIN directors d ON d.movieid = m.id
LEFT JOIN directors d2 ON d2.movieid = 1;

UPDATE since the OP added further information: In order to prevint a second JOIN, you can use a CASE WHEN construct instead. Here the first option, just using fix default values:

SELECT m.id, m.title, 
CASE WHEN d.id IS NULL THEN 1 ELSE d.id END AS directorId, 
CASE WHEN d.name IS NULL THEN 'Steve' ELSE d.name END AS name,
CASE WHEN d.movieid IS NULL THEN 1 ELSE d.movieid END AS movieid
FROM movies m
LEFT JOIN directors d ON d.movieid = m.id;

If you even want to select the default values instead of providing fix values, you can use sub queries within the CASE WHEN part:

SELECT m.id, m.title, 
CASE WHEN d.id IS NULL THEN 1 ELSE d.id END AS directorId, 
CASE WHEN d.name IS NULL THEN (SELECT name FROM directors WHERE id = 1) 
ELSE d.name END AS name,
CASE WHEN d.movieid IS NULL THEN (SELECT movieid FROM directors WHERE id = 1)  
ELSE d.movieid END AS movieid
FROM movies m
LEFT JOIN directors d ON d.movieid = m.id;

Use DISTINCT or LIMIT 1 or similiar within the sub queries if necessary, this depends on your setup. Please see working fiddle here: db<>fiddle