Is there a way to write an OR condition so that the second criteria is only checked if the first criteria yields no results?
For example:
CREATE TABLE some_table (
column_a VARCHAR(10),
column_b VARCHAR(10));
INSERT INTO some_table
VALUES ('A1', 'B1'), ('A2', 'B2');
I'd want this query to return only row with A1, B1, because it found a match using the first criteria:
SELECT * FROM some_table
WHERE column_a = 'A1' OR column_b = 'B2';
I'd want this query to return only row with A2, B2, because there was no match on the first criteria:
SELECT * FROM some_table
WHERE column_a = 'X1' OR column_b = 'B2';
Or perhaps there's a solution that doesnt use the OR operator?
I know you can execute multiple queries like in this example, but that's not ideal when the queries are much larger.
Another possible solution might be to use dynamic SQL, so you can store the majority of the query (without the second or criteria) as a variable execute it, and if no results, append the OR criteria and execute it again. This would prevent duplication of code in larger queries, but I'm sure there must be a better solution.
write an OR condition so that the second criteria is only checked if the first criteria yields no results
The where predicate clause is correctly phrased with OR. If you want just one row in the resultset, we can order by a conditional expression that puts first rows that match the first condition, then limit:
SELECT *
FROM some_table
WHERE column_a = 'A1' OR column_b = 'B2'
ORDER BY CASE WHEN column_a = 'A1' THEN 1 ELSE 2 END
LIMIT 1
If there might be several rows matching on a given criteria, then you might want to add another column to the ORDER BY clause to make the sort predictable, say primary key column id:
ORDER BY CASE WHEN column_a = 'A1' THEN 1 ELSE 2 END, id
LIMIT 1
Alternatively, if you want to retain all rows that match the given criteria, then you use window functions and fetch instead:
ORDER BY RANK() OVER(ORDER BY BY CASE WHEN column_a = 'A1' THEN 1 ELSE 2 END)
FETCH FIRST ROW WITH TIES