CODEWITHSUNDEEP
sqlsql-server
Alrick
Alrick

Reputation: 79

If select returns nothing then

I'm trying to write select as:

SELECT * FROM table1 a 
  WHERE a.d > (
    SELECT b.d FROM table2 b 
     WHERE a.id = b.id and a.Something = 1
  )

BUT if the nested select does not returns any value so SELECT does not returns anything either.

Is possible to write something like:

SELECT * FROM table1 a WHERE a.d > 
(SELECT * FROM 
   IF EXISTS (
        SELECT b.d FROM table2 b WHERE a.id = b.id and a.Something = 1
   )
      SELECT b.d FROM table2 b WHERE a.id = b.id and a.Something = 1 )
   ELSE 
      SELECT '0'
)

Upvotes: 2

Views: 169

Answers (1)

Praveen
Praveen

Reputation: 9335

You can use COALESCE

SELECT * 
FROM table1 a 
WHERE a.d > COALESCE((SELECT b.d FROM table2 b WHERE a.id = b.id and a.Something = 1), '0')

COALESCE

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

See this link for more info.

Upvotes: 2

Related Questions