Oracle SQL - IN Clause display all record given in IN condition even when data not present in table

Question

I have a table customer as described below.

customer_id number customer_name varchar(30) city varchar(20)

columns. When the below query run in Oracle db

SELECT customer_id, city FROM customer WHERE city IN ('abc', 'def', 'ghi')

I'm getting the output as shown below. Table doesn't have record for ghi

customer_id, city 1, abc 2, def

I'm trying to form the output something as below.

customer_id, city 1, abc 2, def null, ghi

Though no record for ghi in table, want to display it in SELECT query output with rest of the column value as null.

Much appreciate help in writing sql for this scenario.

you can. But, why do you want that ? Also, which database are you using ? — Ravi
– Ravi, Commented Oct 2, 2017 at 14:42
IN doesnt generate a list.. customer IN ('abc', 'def', 'ghi') works like customer = 'abc' OR customer = 'def' OR customer = 'ghi' — Raymond Nijland
– Raymond Nijland, Commented Oct 2, 2017 at 14:45
That is not your query, if you are getting any input from it. Why? Because the query you wrote is syntactically incorrect. The IN condition needs to be in a WHERE clause, but there is no WHERE clause in your query. — user5683823
– user5683823, Commented Oct 2, 2017 at 14:47

sagi · Accepted Answer · 2017-10-02 14:52:16Z

5

You need a derived table containing all possibilities. Either you have one, like a city table and then you can do:

SELECT t.customer_id,s.city
FROM city_table s
LEFT JOIN customer t
 ON(s.id = t.city) 
WHERE s.city IN ('abc', 'def', 'ghi')

Or generate the values your self:

SELECT t.customer_id,s.city
FROM (SELECT 'abc' as id FROM DUAL
      UNION ALL 
      SELECT 'def' FROM DUAL
      UNION ALL
      SELECT 'ghi' FROM DUAL) s
LEFT JOIN customer t
 ON(s.id = t.city) 
WHERE s.id IN('abc', 'def', 'ghi')

edited Oct 2, 2017 at 14:52

answered Oct 2, 2017 at 14:43

sagi

40.6k7 gold badges66 silver badges87 bronze badges

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

12 Comments

Ravi Over a year ago

But, there is no record

sagi Over a year ago

@Ravi and? The city table will populate it, and you commented to fast :S there's already an edit.

isaace Over a year ago

@sagi who says it will always be 'ghi?'

sagi Over a year ago

No one, that's why I gave two options. If options one is not possible, then there's no other way to do it then option two. @isaace

sagi Over a year ago

Not that I can think of. What you can do, is create a table with all the values need to be checked, and compare against it. @SriramM

|

Collectives™ on Stack Overflow

Oracle SQL - IN Clause display all record given in IN condition even when data not present in table

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related