CODEWITHSUNDEEP
sqlpostgresqljoin
Charlie
Charlie

Reputation: 469

Left join if value is NULL, otherwise inner join

I want a join on two tables that replicates left join behaviour if a value is NULL, and inner join behaviour if the value is NOT NULL. For example, for the tables:

t1(val) AS (VALUES (NULL)),
t2(val) AS (VALUES ('a'), ('b'))

The join would return a single row with values (NULL,NULL) (as per a left join). For the tables:

t1(val) AS (VALUES ('c')),
t2(val) AS (VALUES ('a'), ('b'))

No rows would be returned (as per an inner join). For the tables:

t1(val) AS (VALUES ('a')),
t2(val) AS (VALUES ('a'), ('b'))

A single row with values ('a','a') would be returned (as per either type of join).

What is the most efficient way to do this?

EDIT: As part of maximising efficiency, I'm looking for a query that doesn't post-filter (i.e. use the WHERE clause).

Upvotes: 0

Views: 2897

Answers (1)

Pham X. Bach
Pham X. Bach

Reputation: 5442

You could use this:

SELECT t1.val, t2.val
FROM t1
LEFT JOIN t2
ON t1.val = t2.val 
WHERE t1.val IS NULL OR t1.val = t2.val;

Or: 

SELECT NULL AS val1, NULL AS val2
FROM t1 
WHERE val IS NULL 
UNION ALL 
SELECT t2.val, t2.val 
FROM t2
INNER JOIN t1 ON t1.val = t2.val;

Upvotes: 2

Related Questions