MySQL - select rows where value = X, or if no matches, where value = null

user_2889165 0
Viewed 1050

How can I find all rows in a table where val_b has a specific value, but if no such row exists, I would like to see if there are rows that match with less specificity, thus where val_b is null.

From the table below, I would like to select where val_a = X and val_b = T and get the row with ID:1 back.

If I select where val_a = X and val_b = V and get row with ID:3 and ID:4 back. Since row 1 and 2 didn't match, I will settle with the rows that have less specificity but still matches val_a.

| ID | val_a | val_b |
| -- | ----- | ----- |
| 1  | X     | T     |
| 2  | X     | U     |
| 3  | X     | null  |
| 4  | X     | null  |
| 5  | Y     | null  |

Is this possible to do directly in the DB query? Something like a syntactically left-associative XOR operator...

mysql
1 Answers

Try this query:

SELECT ID, val_a, val_b
FROM yourTable
WHERE
    val_a = 'X' AND (val_b = 'V' OR
                     (NOT EXISTS (SELECT 1 FROM yourTable
                              WHERE val_a = 'X' AND val_b = 'V') AND val_b IS NULL));

enter image description here

Demo

The logic here is that we retain a record if one of the following conditions be true:

  • val_b matches the intended (non NULL) value, or
  • val_b is NULL and there are no matching non NULL val_b records

The EXISTS clause covers the second case, and it asserts there are no matching non NULL val_b records.

When searching for val_a = 'X' and val_b = 'T' only the ID:1 record was returned using the above query.

If you are using MySQL 8+ then we can use analytic functions here:

WITH cte AS (
    SELECT ID, val_a, val_b,
        COUNT(CASE WHEN val_a = 'X' AND val_b = 'V' THEN 1 END)
            OVER () cnt
    FROM yourTable
)

SELECT ID, val_a, val_b
FROM cte
WHERE val_a = 'X' AND (val_b = 'V' OR (val_b IS NULL AND cnt = 0));

Demo

But note that we still need to use a subquery, even in this case.

user_18632290
Related