CODEWITHSUNDEEP
sqloracle-database
Crates
Crates

Reputation: 81

How to make a select statement return null if no value is returned?

Let's say we have the table "letters" like so:

a | b  
-----  
0 | 0  
0 | 1  
1 | 0  
1 | 1

And the following select statement:

SELECT val1, val2, x.a. val3
  FROM table1, 
       table2, 
       (SELECT a 
          FROM letters 
         WHERE a=b 
           AND a=0) x, 
       table3
 WHERE table1.val1 = table2.val1
   AND table1.val2 = table3.val3

I need the embeded SELECT statement (x) to return:

0  
NULL  
NULL  
NULL

Instead of what is currently returning which is 

0

Then I want to be able to use that in the big SELECT statement. I hope this makes sense!

Upvotes: 6

Views: 79733

Answers (5)

Omar
Omar

Reputation: 65

Remember, if you're using a GROUP BY clause and you want to conditionally replace values with NULL based on some condition, you might need to use a subquery in your FROM clause

Upvotes: 0

Eric
Eric

Reputation: 23

going off of Dash's comment: 

SELECT
CASE 
    WHEN a = 1 OR b = 1 THEN Null
    ELSE 0
END
FROM x

as this would catch your second situation of b = 1, a = 0 and wanting a return of NULL.

Upvotes: 0

Sentinel
Sentinel

Reputation: 6459

The WHERE clause acts as a filter defining which rows are returned, but what you are asking is to have all rows returned, and apply a function on the returned rows to define the values returned for each row.

SELECT table1.val1
     , table1.val2
     , x.a
     , table3.val3
  FROM table1
  JOIN table2
    on table1.val1 = table2.val1
  JOIN table3
    on table1.val2 = table3.val3
 CROSS JOIN (SELECT CASE WHEN a = b AND a = 0 THEN a END AS a
               FROM letters) x

Upvotes: 0

dash
dash

Reputation: 91540

There's a couple of ways you can go about this;

Trivial, using the CASE statement;

SELECT
    CASE 
        WHEN a = b AND a = 0 THEN a
        ELSE NULL
    END
FROM x

Returns:

0
NULL
NULL
NULL

Using a LEFT JOIN:

SELECT  X1.a
FROM    x 
LEFT JOIN   (SELECT a, b FROM x WHERE a = b AND a = 0) AS X1 ON x.a = X1.a AND x.b = X1.b

Returns:

0
NULL
NULL
NULL

Upvotes: 5

aarbor
aarbor

Reputation: 1544

If you want more than one column returned in the inner select, you'd need to specify those columns in the inner select as well...

(SELECT a, NULL 'colname1', NULL 'colname2'
          FROM letters 
         WHERE a=b 
           AND a=0) x,

You can access those columns using x.colname

If the columns could also have values besides NULL, you just need to add the proper joins and return the column values as a normal select statement would.

Upvotes: -3

Related Questions