CODEWITHSUNDEEP
sqloracle-database
Coat
Coat

Reputation: 717

Exists, or Within

I'm writing a some filtering logic that basically wants to first check if there's a value in the filter table, then if there is return the filtered values. When there isn't a value in the filter table just return everything. The following table does this correctly but I have to write the same select statement twice.

select *
  from personTbl
 where (not exists (select filterValue from filterTable where filterType = 'name') or
        personTbl.name in (select filterValue from filterTable where filterType = 'name'))

Is there some better way to do this that will return true if the table is empty, or the value is contained within it?

Upvotes: 2

Views: 47

Answers (2)

MT0
MT0

Reputation: 167774

You can use a collection to try to make the query more intuitive (and only require a single select from the filter table):

CREATE TYPE filterlist IS TABLE OF VARCHAR2(100);
/

SELECT p.*
FROM   PersonTbl p
       INNER JOIN
       ( SELECT CAST(
                  MULTISET(
                    SELECT filterValue
                    FROM   filterTable
                    WHERE  filterType = 'name'
                  )
                  AS filterlist
                ) AS filters
         FROM   DUAL ) f
       ON ( f.filters IS EMPTY OR p.name MEMBER OF f.filters );

Upvotes: 1

ruakh
ruakh

Reputation: 183211

One approach is to do a left outer join to your filter-subquery, and then select all the rows where the join either failed (meaning that the subquery returned no rows) or succeeded and had the right value:

SELECT personTbl.*
  FROM personTbl
  LEFT
 OUTER
  JOIN ( SELECT DISTINCT filterValue
           FROM filterTable
          WHERE filterType = 'name'
       ) filter
    ON 1 = 1
 WHERE filter.filterValue = personTbl.name
    OR filter.filterValue IS NULL
;

To be honest, I'm not sure if the above is a good idea — it's not very intuitive1, and I'm not sure how well it will perform — but you can judge for yourself.

1. As evidence of its unintuitiveness, witness the mistaken claim below that it doesn't work. As of this writing, that comment has garnered two upvotes. So although the query is correct, it clearly inspires people to great confidence that it's wrong. Which is a nice party trick, but not generally desirable in production code.

Upvotes: 4

Related Questions