Oracle SQL show null value

Question

I am trying to do the following query:

SELECT column1,column2,column3
FROM tablename
WHERE
column1 IN
('string1','string2','string3','string4')
AND column2='some_value';

The results show only: String1, string2,string3 because string4 does not have an equivalent some_value in column2.

How can I show all 4 values with the 4th showing as null like:

Column1|column2|column3
+----------------------+
|string1 value  value  |
|string2 value  value  |
|string3 value  value  |
|string4 null   null   |
+----------------------+

alternatively ... AND (nvl(column2, 'some value') = 'some value') — crowne
– crowne, Commented Aug 18, 2015 at 17:45
You may need to be more specific about your intent. What is your spec? What is the rule that makes it that row #4 should be returned as well? The current answers are making educated guesses, but they are still only guesses unless you explicitly state the logic you want to apply to each row. — sstan
– sstan, Commented Aug 18, 2015 at 17:59

Vamsi Prabhala · Accepted Answer · 2015-08-18 17:45:16Z

1

SELECT column1,column2,column3
FROM tablename
WHERE
column1 IN
('string1','string2','string3','string4')
AND column2='some_value'
OR (column2 is null or column3 is null);

You should saycolumn2 is null at the end. as it doesn't correspond to your where condition.

answered Aug 18, 2015 at 17:45

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

1 Comment

sstan Over a year ago

It's hard to know if the query is correct because I'm not even sure what OP is actually looking for. However, I suspect you didn't mean to code the query such that nulls in column2 or column3 cause the row to return regardless of the value in column1. You may be missing an extra set of brackets.

Juan Carlos Oropeza · Accepted Answer · 2015-08-18 17:45:28Z

1

Add OR

SELECT column1,column2,column3
FROM tablename
WHERE
    column1 IN ('string1','string2','string3','string4')
AND (
        column2='some_value'
     OR column2 is null
    );

answered Aug 18, 2015 at 17:45

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Comments

Diogo Maschio · Accepted Answer · 2015-08-18 20:14:28Z

0

In this case the treatment is diferent, if value of column2 is only diferent from "some value" (parameter) return all lines. - In this case: - column2 with diferent value from parameter will be returned with empty value on column2 and column3. - column2 with null value will be returned

SELECT column1, CASE WHEN column2 = 'some value' THEN COLUMN2 ELSE NULL END column2, CASE WHEN column2 = 'some value' THEN column3 ELSE NULL END column3 FROM TABLENAME WHERE column1 IN ('string1','string2','string3','string4') AND (column2='value' OR column2 = COLUMN2 OR column2 IS null);

answered Aug 18, 2015 at 20:14

Diogo Maschio

746 bronze badges

Collectives™ on Stack Overflow

Oracle SQL show null value

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related