If my search term is not found add to array

Question

Looping through a list of terms and searching database for each term. Is there a way to return a list of search terms that didn't find results in the database?

This is my SELECT query:

SELECT x_tbl.x_str
    FROM x_tbl
    LEFT JOIN p_tbl ON p_tbl.p_id = x_tbl.p_id
    LEFT JOIN t_tbl ON t_tbl.t_id = x_tbl.t_id
    WHERE t_tbl.t = %(uw)s
    OR    t_tbl.ta = %(uw)s
    OR    %(uw)s = ANY (t_tbl.tb)
    OR    t_tbl.tc = %(uw)s
    OR    p_tbl.pa = %(uw)s
    OR    %(uw)s = ANY (p_tbl.pb)

I want to return uw if it does not find any results in the database. Is this possible?

Erwin Brandstetter · Accepted Answer · 2020-08-01T23:03:00.007

I want to return uw if it does not find any results in the database.

For one search term

The two LEFT [OUTER] JOIN between x_tbl, p_tbl and t_tbl do not eliminate any rows by themselves. Two times NOT EXISTS returns a list of search terms, that cannot find anything:

SELECT %(uw)s  -- old-style Python placeholder
WHERE  NOT EXISTS (
   SELECT FROM p_tbl
   WHERE  pa     = %(uw)s
      OR  %(uw)s = ANY (p.pb)
   )
AND    NOT EXISTS (
   SELECT FROM t_tbl
   WHERE  %(uw)s IN (t, ta, tc)
      OR  %(uw)s = ANY (tb)
   );

If there can be orphans in t_tbl and/or p_tbl (not linked to any row in x_tbl), the set may be bigger, and the query gets more expensive:

SELECT %(uw)s  -- old-style Python placeholder
WHERE  NOT EXISTS NOT EXISTS (
   SELECT FROM x_tbl JOIN p_tbl p USING (p_id)
   WHERE  p.pa     = %(uw)s
      OR  %(uw)s = ANY (p.pb)
   )
AND (
   SELECT FROM x_tbl JOIN t_tbl t USING (t_id)
   WHERE  %(uw)s IN (t.t, t.ta, t.tc)
      OR  %(uw)s = ANY (t.tb)
   );

This is dealing with one search term at a time, like your original query. You mentioned a list. Running a single query for all of them might be (much) cheaper ...

One query to rule them all

Pass the list as array (Postgres array literal) - which may require an explicit cast (::text[]) - unnest() and attach the same WHERE conditions as above:

SELECT uw
FROM   unnest(%(my_list_as_array)s::text[]) q(uw)
WHERE  NOT EXISTS (
   SELECT FROM p_tbl
   WHERE  pa = q.uw
      OR  q.uw = ANY (p.pb)
   )
AND    NOT EXISTS (
   SELECT FROM t_tbl
   WHERE  q.uw IN (t, ta, tc)
      OR  q.uw = ANY (tb)
   );

Or, including the join to tbl_x, same as above:

SELECT uw
FROM   unnest(%(my_list_as_array)s::text[]) q(uw)
WHERE  NOT EXISTS (
   SELECT FROM x_tbl JOIN p_tbl p USING (p_id)
   WHERE  p.pa     = q.uw
      OR  q.uw = ANY (p.pb)
   )
AND    NOT EXISTS (
   SELECT FROM x_tbl JOIN t_tbl t USING (t_id)
   WHERE  q.uw IN (t.t, t.ta, t.tc)
      OR  q.uw = ANY (t.tb)
   );

Basics:

Select rows which are not present in other table

You may want to keep array elements in original order, or even attach an ordinal position. See:

PostgreSQL unnest() with element number

Aside, your original query can multiply rows - if there can be more than one row on the right side of each join. See:

Two SQL LEFT JOINS produce incorrect result

If my search term is not found add to array

1 Answers1

For one search term

One query to rule them all