All geek questions in one place

SQL - if Select returns nothing, then make another choice.

I'm currently trying to execute an SQL query that can detect if a SELECT query returns nothing, and then do another one if that is the case.

Here is what I mean:

IF SELECT * FROM table WHERE criteria = criteria RETURNS NO ROWS THEN SELECT * FROM table WHERE criteria2 = criteria2

Is it possible? I don’t think the empty answer is considered “null”, so I'm a little worried about that.

+5
source share
2 answers

You can do this in one statement, assuming the columns are the same:

 SELECT * FROM table WHERE criteria = criteria UNION ALL SELECT * FROM table WHERE criteria2 = criteria2 AND NOT EXISTS (SELECT 1 FROM table WHERE criteria = criteria);
+6
source

You can use NOT EXISTS :

 SELECT * FROM table WHERE criteria2 = criteria2 and not exists ( SELECT * FROM table WHERE criteria = criteria ) union all SELECT * FROM table WHERE criteria = criteria;

Here NOT EXISTS provides the return of any part of UNION ALL. If the second criteria = criteria passes, then NOT EXISTS will return false and, therefore, only the second part of the above query will return the result. If this is not so, then there are no rows with criteria = criteria , and NOT EXISTS will return true and, therefore, only the data of the first part will be returned.

+3
source

Source: https://habr.com/ru/post/1263915/

More articles:


All Articles