Search code examples
sqlteradata

SQL returning on values that do not exist

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
Solution

  • 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;