Danny David Leybzon Danny David Leybzon - 14 days ago 7
SQL Question

How can I return a column that says "No" for values missing from the result?

Sorry for the poor phrasing in the title; I can't figure out how to explain my question succinctly.

I have a list of

fruit
(
apple
,
banana
,
pear
) and want to create a table which looks like:

fruit eaten

apple yes
banana yes
pear no


from a table that looks like

fruit quantity

apple 120
banana 30


(Note that the original table does not include pears). So far I've figured out how to get to:

fruit eaten

apple yes
banana yes


by the query:

select
fruit,
case when quantity > 0 then "yes" else no end as eaten
from original_table


But I can't figure out how to get those pesky pears to be included.

Answer

If you have a fruit table you could do something like this:

SELECT
    f.fruit
    ,CASE WHEN ot.quantity > 0 THEN 'yes' ELSE 'no' END as Eaten
FROM
    fruit f
    LEFT JOIN original_table ot
    ON f.fruit = ot.fruit

If you do not have a fruit table then you need to make one. I am not too familiar with hive but this technique should work in lots of different platforms:

SELECT
    f.fruit
    ,CASE WHEN ot.quantity > 0 THEN 'yes' ELSE 'no' END as Eaten
FROM
    (
       SELECT 'apple' as fruit
       UNION ALL
       SELECT 'banana'
       UNION ALL
       SELECT 'pear'
    ) f
    LEFT JOIN original_table ot
    ON f.fruit = ot.fruit
Comments