Display one row per search term, subsitute default if not found

Question

Query:

SELECT product_id,
       name 
  FROM product 
 WHERE barcode in (681027, 8901030349379,
                   679046, 679047,
                   679082, 679228,
                   679230, 679235,
                   679236, 679238,
                   679328, 679330,
                   679528, 679608,
                   679609, 679647,
                   679727, 679728,
                   679730, 679808,
                   679809, 679828, 679830)

Output:

YES   -IF PRODUCT NAME IS PRESENT
NO    -IF PRODUCT NAME IS NOT PRESENT
NO
YES
YES
NO
...                    

How do I display if there is no row for the particular barcode? Is it good practice to use IN statement in sql?

I use PostgreSQL 8.2.11.

Answer 1

This might work:

select case when p.name is null then 'NO' else 'YES' end as barcode_exists, t.barcode
from product as p
right join (
    select 681027 as barcode union
    select 8901030349379 union
    select 679046 union
    select 679047 union
    select 679082 union
    select 679228 union
    select 679230 union
    select 679235 union
    select 679236 union
    select 679238 union
    select 679328 union
    select 679330 union
    select 679528 union
    select 679608
            -- all the rest barcodes
) as t on p.barcode = t.barcode

In union put all barcodes that you want to check.

Aded:

It returns two columns to match barcode and answer because you can't relay on rows order unless you specify one.

Answer 2

Since PostgreSQL 8.4

Use unnest() and a LEFT JOIN:

SELECT x.barcode
     , p.product_id
     , CASE WHEN p.name IS NULL THEN 'NO' ELSE 'YES' END AS product_name_exists
FROM  (
   SELECT unnest (
         '{679046,679047,679082,679228,679230,679235,679236,'
          '679238,679328,679330,679528,679608,679609,679647,'
          '679727,679728,679730,679808,679809,679828,679830}'::text[]) AS barcode
         ) x
LEFT   JOIN product USING (barcode);

unnest() generates one row per array element. It was introduced with PostgreSQL 8.4.
LEFT JOIN ensures that every barcode shows up, even if no matching product is found.

Be aware that there can be two reasons for a "missing" product name (NULL):

1. No matching product.barcode is found.
2. product.barcode is found, but the product.name coming with it is NULL.

You may want to add a NOT NULL constraint to the column name in your table product - if you don't have one.

PostgreSQL 8.3

Use regexp_split_to_table():

SELECT x.barcode
     , p.product_id
     , CASE WHEN p.name IS NULL THEN 'NO' ELSE 'YES' END AS product_name_exists
FROM   (SELECT regexp_split_to_table (
              '679046,679047,679082,679228,679230,679235,679236,'
              '679238,679328,679330,679528,679608,679609,679647,'
              '679727,679728,679730,679808,679809,679828,679830', ',') AS barcode
         ) x
LEFT   JOIN product USING (barcode);

Note how strings listed on separate lines are concatenated automatically. A not so well known standard SQL feature.

PostgreSQL 8.2

None of the SET-generating functions exist, yet. Consider upgrading to a more recent version! PostgreSQL 8.2 is reaching end of life in Dezember 2011.

Generic solution for any number of barcodes (slow for very big numbers of barcodes):

SELECT x.barcode
     , p.product_id
     , CASE WHEN p.name IS NULL THEN 'NO' ELSE 'YES' END AS product_name_exists
FROM (
   SELECT a[i] AS barcode
   FROM  (
      SELECT a.a, generate_series(1, array_upper(a.a, 1)) AS i
      FROM  ( 
         SELECT '{679046,679047,679082,679228,679230,679235,679236,'
                 '679238,679328,679330,679528,679608,679609,679647,'
                 '679727,679728,679730,679808,679809,679828,679830}'::text[] AS a
            ) a
        ) i
    ) x
LEFT   JOIN product USING (barcode);

Or chain SELECTs with UNION ALL:

SELECT x.barcode
      ,p.product_id
      ,CASE WHEN p.name IS NULL THEN 'NO' ELSE 'YES' END AS product_name_exists
FROM (
    SELECT           '679046' AS barcode
    UNION ALL SELECT '679047'
    UNION ALL SELECT '679082'
    UNION ALL SELECT '679228'
    UNION ALL SELECT '679230'
    UNION ALL SELECT '679235'
    UNION ALL SELECT '679236'
-- ...
    ) x
LEFT   JOIN product USING (barcode);

Use UNION ALL, not UNION. You don't want to try and eliminate duplicates with every added barcode. That's slow with large numbers. Related:

• Selecting arrays in nicely printed format in SQL