CODEWITHSUNDEEP
sqljoinunion
EnzoR
EnzoR

Reputation: 3346

Filtering by JOIN, UNION, INTERSECT, EXCEPT

I have these two tables:

CREATE TABLE set (
  id INT PRIMARY KEY;
  value TEXT NOT NULL
);

CREATE TABLE filter (
  id INT PRIMARY KEY
);

The set TABLE contains some information tied to an id. A very large set in the reality. The filter TABLE should act like a filter as explained below. If any, it will contain a subset of the id values found in the set TABLE.

What I need to do in a single query is:

  1. If the filter is empty, I need to get all elements in the set (negative filter).
  2. If the filter is not empty, I need to get the INNER JOIN, thus a proper filtering.

I would prefer not to discriminate on COUNT(*) on the filter but rather exploit JOINs, UNIONs, INTERSECTs and EXCEPTs.

How would you write such a query?

Upvotes: 1

Views: 749

Answers (1)

sgeddes
sgeddes

Reputation: 62831

Here's one approach using an outer join with not exists:

select s.id, s.value
from set s
    left join filter on s.id = filter.id 
where not exists (select 1 from filter) or s.id = filter.id

It's a little counter intuitive -- but basically, if no records exist in the filter table, return all. Else, return only those that match...

Upvotes: 2

Related Questions