Sorry i am new to SQL and was wondering if there exists a means to returns a default value for record that do not exist. e.g. select * from tables where col1 in (1,2,3) in this case 2 does not exists in the table
col1 | fruit |
---|---|
1 | apples |
3 | bananas |
2 | sorry this value does not exist in the table |
The general pattern you would follow here would be an anti-join, where you start with a set (table) of all IDs, and then left join to your table above. In lieu of a formal table, we can use an inline CTE for the former source of IDs table.
WITH ids AS (
SELECT 1 AS col1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
)
SELECT
t1.col1,
COALESCE(t2.fruit, 'sorry this value does not exist in the table') AS fruit
FROM ids t1
LEFT JOIN yourTable t2
ON t2.col1 = t1.col1;