SQL: Mark which WHERE condition matched

Question

This is a theoretical question, I was wondering if there is a good way of finding out which condition in the WHERE statements matched.

Say I have a query like this:

SELECT * FROM table WHERE
    COND1 OR
    (COND2 AND COND3) OR
    COND4

Is there any way of knowing which of the conditions made a given row match (or unmatch)?

---

One solution i thought of was adding a CASE clause, to the SELECT, and rerunning all the WHERE conditions for that row:

SELECT *, which_cond = CASE .... END CASE ...

Answer 1

CASE is a possibility. But shorter to write would be IF() (at least with MySQL):

SELECT *, IF(COND2, 1, 0) AS cond2, IF(COND3, 1, 0) as cond3 ... 

and every cond* matched if its value is 1.

Of course it does not make sense to check whether COND1 matches or not. For all results you get back, COND1 was true. So IF(COND1, 1, 0) will always return 1 (in your example).

Update:
Again at least for MySQL, I discovered, that the following is sufficient if you only want to get either 1 or 0 as result:

SELECT *, COND2 AS cond2, COND3 as cond3 ...

and with respect to Mark's answer, you can avoid writing the conditions twice, if you use HAVING instead of WHERE (HAVING as access to aliases):

SELECT *, COND2 AS cond2, COND3 as cond3
FROM table
HAVING COND1 AND (cond2 OR cond3)

(note: uppercase means the actual condition expression and lowercase is the alias name)

Update 2:

Well it changes not that much: you only have to check conditions that are connected via OR in your updated version it would be sufficient to check whether COND2 is true or not. If so, then COND3 is also true and vice versa.

SELECT *, COND1 AS cond1, COND2 as cond2, COND4 as cond4
FROM table
HAVING cond1 OR (cond2 AND COND3) OR cond4

I think the point is clear.

Answer 2

Your method works but it requires you to type out all the conditions twice, and if they are complicated expressions that could be annoying to do. Duplication of code also leads to bugs when the conditions need to be changed and you forget to update both places. I'd suggest instead using a subselect to evaluate the conditions and than using aliases from there on:

SELECT *,
       CASE WHEN Cond1 AND Cond2 THEN 'Foo'
            ELSE 'Bar'
       END AS WhichCondition
FROM (SELECT *,
           (...SQL FOR COND1...) AS Cond1,
           (...SQL FOR COND2...) AS Cond2,
           (...SQL FOR COND3...) AS Cond3
           FROM table) AS T1
WHERE Cond1 AND (Cond2 OR Cond3)

On SQL Server and Oracle you could use a CTE instead of a subquery. I didn't do this because you also used the MySql tag.