Menu
I'm currently trying to do 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 this possible? I don't think that an empty reply counts as "null" so I have a bit of a trouble with that.
395
The common table expression ( WITH clause) wraps the first query that we want to execute no matter what. We then select from the first query, and use UNION ALL to combine the result with the result of the second query, which we're executing only if the first query didn't yield any results (through NOT EXISTS ).
You can use @@ROWCOUNT. For e.g. You will get 0 if first statement will not return any rows. You can also use if statement to check that just after first statement.
The SQL EXCEPT operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The EXCEPT operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.
select without from , on the other hand, works on four of them. By now you might wonder why stand-alone values might be useful at all. As I implied above, it is more powerful than select without from because it is not limited to produce a single row. With select without from , you'd need to use union .
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
