Return null rows for non-matched values in IN clause in SQL

Question

I have a query that returns records if found values in the IN clause. The matched records my vary from 0 to 1 and so thus the result of the query. But I want make the count as 5, even if rest rows are filled with NULL. Is there any way I can achieve this? As, for the given query

SELECT categoryID, categoryName
FROM category
WHERE categoryID
IN ( 52, 58, 60 , 62 , 64)

I have values for the first 3 ids only and thus the result gives me 3 rows only. But I want the count of result to be 5 and the rest non-satisfying rows filled with null values to make the count. The current result:

CategoryID  CategoryName
52           ABC
58           XYZ
60           DEF

The required result

CategoryID  CategoryName
52           ABC
58           XYZ
60           DEF
NULL         NULL
NULL         NULL

I dont understand why you want to do that...? So if you will type IN ( 52, 58, 60 , 62 , 64, 85, 62, 82) its just returns more null vaules...? — Nightmaresux
– Nightmaresux, Commented Nov 27, 2014 at 9:37
While it's possible to do it directly in SQL, in most cases, it would be much easier to do it outside of SQL. @Nightmaresux's comment points out one other good question: what do you want to happen when the same ID is specified multiple times? — user743382
– user743382, Commented Nov 27, 2014 at 9:38
This might be achievable using code but I wanted to know if we could do so directly in plain SQL — Aakash Jain
– Aakash Jain, Commented Nov 27, 2014 at 9:41

Barmar · Accepted Answer · 2014-11-27 09:46:21Z

3

Use a LEFT JOIN with a synthesized table that lists all the category IDs you want:

SELECT c.categoryID, c.categoryName
FROM (SELECT 52 AS categoryID
      UNION SELECT 58 UNION SELECT 60 UNION SELECT 62 UNION SELECT 64) AS a
LEFT JOIN category AS c ON a.categoryID = c.categoryID

Another way to do this is to create a table that contains all possible category IDs. Then use:

SELECT c.categoryID, c.categoryName
FROM allCategories AS a
LEFT JOIN category AS c ON a.categoryID = c.categoryID
WHERE a.categoryID IN (52, 58, 60 , 62 , 64)

edited Nov 27, 2014 at 9:46

answered Nov 27, 2014 at 9:40

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

2 Comments

user743382 Over a year ago

The OP wants categoryID to be NULL too if it's not found, so I think it should be c.categoryID rather than a.categoryID.

Barmar Over a year ago

@hvd Good catch, that's a pretty strange requirement.

overflowed · Accepted Answer · 2014-11-27 09:52:43Z

1

Add 5 null rows to the end and just select the first 5 rows.

SELECT categoryID, categoryName FROM (
SELECT categoryID, categoryName, 1 as ord 
FROM category
WHERE categoryID
IN ( 52, 58, 60 , 62 , 64)
UNION ALL
SELECT NULL as categoryID, NULL as categoryName, 0 as ord 
UNION ALL
SELECT NULL as categoryID, NULL as categoryName, 0 as ord 
UNION ALL
SELECT NULL as categoryID, NULL as categoryName, 0 as ord  
UNION ALL
SELECT NULL as categoryID, NULL as categoryName, 0 as ord  
UNION ALL
SELECT NULL as categoryID, NULL as categoryName, 0 as ord  )t 
ORDER BY ord desc LIMIT 0,5;

edited Nov 27, 2014 at 9:52

answered Nov 27, 2014 at 9:40

overflowed

1,83812 silver badges15 bronze badges

3 Comments

user743382 Over a year ago

UNION removes duplicates, so this doesn't work. Creative answer though, and it can be made to work with UNION ALL.

Aakash Jain Over a year ago

This also handles the repeated ID case

user743382 Over a year ago

@AakashJain You get the same results as with Barmar's first approach: neither will return the same category twice. Barmar's second approach might, depending on how you fill the table.

Collectives™ on Stack Overflow

Return null rows for non-matched values in IN clause in SQL

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related