如何在b=c上左連接a，但如果連接表為NULL，則在(Postgres)SQL中連接x而不是？

鑒于我有兩個 SQL 表：

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

如果我做一個典型的左連接；

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

然后輸出將類似于：

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

因為正如預期的那樣，對于電影2來說沒有。director

但是，在連接導致NULL匹配的情況下，我想提供一個默認值：

在偽 SQL 中：

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

意思是，如果連接導致NULL匹配，則加入（默認）值1，這對我來說是理想的回傳：

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

意思是，如果 JOIN 失敗，我想為所有電影提供默認導演。

是否可以在（Postgres）SQL 中做這樣的事情？

uj5u.com熱心網友回復：

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

小提琴：https ://dbfiddle.uk/?rdbms=postgres_13&fiddle=3b90b49d07a6c50c2638d2c0fc66d72c

uj5u.com熱心網友回復：

由于您需要使用其他連接和操作，因此您需要為每部電影的導演創建一個 CTE，然后使用該 CTE 表而不是 director 表。

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

帶有附加資料的輸出：

看到這個小提琴。

如果在movieid欄位中您需要它與董事表匹配，請在 cte 中添加另一個欄位 ,COALESCE(directors.movieid, d2.movieid) AS dmovieid并在SELECT陳述句中使用它（但不要在聯接中使用它）。例如這里

uj5u.com熱心網友回復：

您可以撰寫兩個JOIN，一個連接現有條目，一個連接其他條目，movieid 為 1，然后使用COALESCE“后備”值替換 NULL 值：

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;

更新，因為 OP 添加了更多資訊：為了防止第二個JOIN，您可以使用CASE WHEN構造來代替。這是第一個選項，僅使用修復默認值：

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;

如果您甚至想選擇默認值而不是提供固定值，則可以在CASE WHEN部件中使用子查詢：

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;

如有必要，在子查詢中使用DISTINCTorLIMIT 1或類似的，這取決于您的設定。請在此處查看作業小提琴：db<>fiddle

標籤：sql PostgreSQL 左连接

上一篇：SQL獲取不在JOIN中的行

下一篇：鏈表樹執行程序中出現錯誤