CODEWITHSUNDEEP
sqlpostgresql
Nicholas Hassan
Nicholas Hassan

Reputation: 969

Include NULL in WHERE Array?

I'm working with left joins that are creating NULL values. Let's say I'm interested in how many users have a basic plan or no plan at all (indicated by a NULL record in the left join)

SELECT u.id, s.plan_type 
FROM users as u
LEFT JOIN subscriptions as s on s.user_id = u.id
WHERE s.plan_type IN (NULL, 'basic')

The above does not work as intended, likely because it doesn't recognize it as NULL. I'd have to write something like WHERE s.plan_type IS NULL OR s.plan_type = 'basic'

I was wondering if there was a short hand for that, or if you always have to use IS NULL to identify NULL values

Upvotes: 0

Views: 82

Answers (2)

Rajat
Rajat

Reputation: 5803

If your table isn't so huge, you could use coalesce to handle null before matching

select u.id, s.plan_type 
from users as u
left join subscriptions as s on s.user_id = u.id
where coalesce(s.plan_type,'basic') = 'basic';

Note that the use of function on the left side of the where clause will make the query unsargable.

Upvotes: 3

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659157

s.plan_type IN (NULL, 'basic') is just shorthand for (s.plan_type = NULL OR s.plan_type = 'basic').

Anything = NULL always returns NULL, not true. And only true qualifies in a WHERE clause. So rephrase:

SELECT u.id, s.plan_type 
FROM   users u
LEFT   JOIN subscriptions s ON s.user_id = u.id
WHERE (s.plan_type = 'basic' OR
       s.plan_type IS NULL);

To be precise, that's only correct if subscriptions.plan_type is defined NOT NULL. Failing that, the resulting NULL value could be just that, not a missing plan.

This alternative formulation does not share the same ambiguity for a resulting NULL:

SELECT u.id, s.plan_type
FROM   users u
JOIN   subscriptions s ON s.user_id = u.id
WHERE  s.plan_type = 'basic'
UNION ALL
SELECT u.id, NULL  -- always means missing
FROM   users u
WHERE  NOT EXISTS (SELECT FROM subscriptions WHERE user_id = u.id);

And it may be faster, because Postgres (like most RDBMS) often doesn't perform well with OR'ed predicates. See about "ugly OR":

If referential integrity is guaranteed with a FK constraint from subscriptions.user_id to users.id, and s.user_id is defined NOT NULL you can omit the users table from the first SELECT:

SELECT user_id AS id, plan_type
FROM   subscriptions
WHERE  plan_type = 'basic'
UNION ALL
SELECT id, NULL
FROM   users u
WHERE  NOT EXISTS (SELECT FROM subscriptions WHERE user_id = u.id);

Might be fastest.

Aside: No array is involved here. You show the form of IN using a list. See:

Upvotes: 3

Related Questions