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;