Return Empty rows when Where clause does not match an entry in PostgreSQL DB

Question

I'm running a simple SELECT statement on my PostgreSQL DB that looks like the following:

SELECT * FROM my_table WHERE primary_key IN (a, b, c);

If my_table only has entries corresponding to the primary keys a, and c, then the query will only return two rows - one for a and all its data, and one for c and all its data.

I want the query to return an empty row (or a NULL/nil value) whenever there is no entry for the primary key in the table. So I want the output of the above SELECT query to be [a_data, nil, b_data] instead of [a_data, b_data]. Is there a way to accomplish this?

More generally, if I have SELECT * FROM my_table WHERE primary_key IN (x_1, x_2, ..., x_n), I want there to be n rows returned, where x_data is NULL/nil for every x that doesn't exist as a key in the DB.

Create a CTE by UNION of all key values and then LEFT JOIN it with your table. — PM 77-1
– PM 77-1, Commented May 13, 2021 at 1:29
But that would still give me non-nil entries when I execute the above SELECT query on the joined table, right? — shubhamgoenka
– shubhamgoenka, Commented May 13, 2021 at 2:03

Gordon Linoff · Accepted Answer · 2021-05-13 15:48:39Z

3

Use a derived table and left join. In Postgres, this looks like:

select t.primary_key, primary_key
from (values (a), (b), (c)) v(primary_key) left join
     my_table t
     using (primary_key);

edited May 13, 2021 at 15:48

answered May 13, 2021 at 2:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

shubhamgoenka Over a year ago

running this on an interactive PostgreSQL DB session gives me the following error: ``` $ SELECT * FROM (VALUES (a), (b), (c)) v(primary_key) LEFT JOIN my_table t USING (primary_key); ERROR: column "a" does not exist LINE 1: SELECT * FROM (VALUES (a), (b), (c)) v(primary_key) L... ^ ```

Erwin Brandstetter Over a year ago

@shubhamgoenka: Missing quotes. With string constants, make that ... VALUES ('a'), ('b'), ('c') ... (Your original example has the same syntax error.)

Gordon Linoff Over a year ago

@shubhamgoenka . . . Your question does not use single quotes on the values, so I did not include them here. If they are literal string values, then use single quotes.

shubhamgoenka Over a year ago

Okay, I've gotten an output that looks like this: ``` my_database=# SELECT * FROM (VALUES ('a'), ('b'), ('c')) v(primary_key) LEFT JOIN my_table t USING (primary_key); primary_key -------------- a b c (3 rows) ``` my table only has one column (i.e., the primary key), so with this solution, I have no way of knowing whether the entries in the output came from the table or not. Is there a way to distinguish, from the output, what was originally in the table vs. what wasn't? Perhaps through a null value instead of b ?

Gordon Linoff Over a year ago

@shubhamgoenka . . . If you select primary_key, you will always get a value. If you select t.primary_key you will see if there is a match.

Collectives™ on Stack Overflow

Return Empty rows when Where clause does not match an entry in PostgreSQL DB

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related