Here’s the situation:
You want to query the status of a member and would always want to return result for your application regardless if it exists or not.
Despite having two queries to do this, we can do it in just one query. In my case, it’s due to my program structure and multiple database connectivity that do not allow me to make multiple queries and checking here and there yada yada to come to the conclusion that, the member doesn’t exists in the database.
Table: member
| ID | Name | Country | Status |
| 111 | Evan | Singapore | Active |
| 222 | Tanny | Malaysia | Active |
| 333 | Dennis | Indonesia | Inactive |
Here’s what we can do
SELECT status FROM member WHERE id = ’444′ UNION SELECT ‘NOT EXISTS’ FROM dual WHERE NOT EXISTS(SELECT 1 FROM member WHERE id = ’444′ )
or you can further simplify it into one query rather than using UNION
SELECT NVL( (SELECT status FROM member WHERE item_id = ’444′ ),’NOT EXISTS’ ) FROM dual
Result:
| Status |
| NOT EXISTS |
Discussion:
The major advantage of doing so is that you can always be assured that the connection to your database is OK and the query result is OK as well. You can be assured that you can always get your result (and return some message if there isn’t), eliminating any other failing factors (db connection problem yada yada yada, of course you need to write your query properly)
Now, if there’s isn’t any result returned, something must have gone wrong with your database!
Alternatively, you can always structure your coding properly, include try and catch exception procedure as well.
