CODEWITHSUNDEEP
sqlsqlite
JTFRage
JTFRage

Reputation: 397

SQL - IN clause with no match

I'm trying to build a query where I can select from a table all products with a certain ID but I would also like to find out what products were not found within the IN clause.

Product Table

ID | Name
---|---------
1  | ProductA
2  | ProductB
4  | ProductD
5  | ProductE
6  | ProductF
7  | ProductG

select *
from products
where id in (2,3,7);

As you can see, product id 3 does not exist in the table. My query will only return rows 2 and 7. I would like a blank/null row returned if a value in the IN clause did not return anything.

Desired Results:

ID | Name
---|---------
2  | ProductB
3  | null
7  | ProductG

Upvotes: 0

Views: 132

Answers (2)

forpas
forpas

Reputation: 164139

IN is not useful in this case.
Use a CTE with the ids that you want to search for and left join to the table:

with cte(id) as (select * from (values (2),(3),(7)))
select c.id, p.name 
from cte c left join products p
on p.id = c.id

See the demo.
Results:

| id  | Name     |
| --- | -------- |
| 2   | ProductB |
| 3   |          |
| 7   | ProductG |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can use a left join:

select i.id, p.name
from (select 2 as id union all select 3 union all select 7
     ) i left join
     products p
     on p.id = i.id

Upvotes: 2

Related Questions