By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE your_table (
fruit VARCHAR(32),
color VARCHAR(32)
);
INSERT INTO
your_table
VALUES
('pear', 'red'),
('apple', 'green'),
('apple', 'golden')
3 rows affected
SELECT *
INTO #temp
FROM your_table
WHERE fruit = 'apple' AND color = 'red'
IF (@@ROWCOUNT = 0)
SELECT *
FROM your_table
WHERE fruit = 'apple'
ELSE
SELECT *
FROM #temp
DROP TABLE #temp
| fruit | color |
|---|---|
| apple | green |
| apple | golden |
SELECT *
FROM your_table
WHERE fruit = 'apple' AND color = 'red'
UNION ALL
SELECT *
FROM your_table
WHERE fruit = 'apple' AND NOT EXISTS (SELECT * FROM your_table WHERE fruit = 'apple' AND color = 'red')
| fruit | color |
|---|---|
| apple | green |
| apple | golden |
SELECT *
FROM your_table
WHERE fruit = 'apple'
AND (
color = 'red'
OR NOT EXISTS (SELECT * FROM your_table WHERE fruit = 'apple' AND color = 'red')
)
| fruit | color |
|---|---|
| apple | green |
| apple | golden |
INSERT INTO
your_table
VALUES
('apple', 'red')
1 rows affected
SELECT *
INTO #temp
FROM your_table
WHERE fruit = 'apple' AND color = 'red'
IF (@@ROWCOUNT = 0)
SELECT *
FROM your_table
WHERE fruit = 'apple'
ELSE
SELECT *
FROM #temp
DROP TABLE #temp
| fruit | color |
|---|---|
| apple | red |
SELECT *
FROM your_table
WHERE fruit = 'apple' AND color = 'red'
UNION ALL
SELECT *
FROM your_table
WHERE fruit = 'apple' AND NOT EXISTS (SELECT * FROM your_table WHERE fruit = 'apple' AND color = 'red')
| fruit | color |
|---|---|
| apple | red |
SELECT *
FROM your_table
WHERE fruit = 'apple'
AND (
color = 'red'
OR NOT EXISTS (SELECT * FROM your_table WHERE fruit = 'apple' AND color = 'red')
)
| fruit | color |
|---|---|
| apple | red |