MINUS algebra operator set in firebird database

Question

I have two equal queries whit different filter applied on the same table.

The first query returns (1,2,3,4,5) and the second returns (3,4,5).

I want apply the MINUS/EXCEPT operator:

select1 minus select2 = (1,2)

How should I implements this logic using firebird SQL dialect ? (I'm using superserver v2.1)

I'm getting the opposite results performing

select1 UNION select2 = (1,2,3,4,5)

thanks

Answer 1

The MINUS operator does not exist in Firebird. The closest approximation I can think of is something like the example below. This uses Common Table Expressions, introduced in Firebird 2.1, but could of course also work with subqueries (I just find CTE more readable)

WITH select1 AS (
   SELECT id, ....
),
select2 AS (
   SELECT id, ....
)
SELECT ...
FROM select1 
LEFT JOIN select2 ON select2.id = select1.id -- more conditions...?
WHERE select2.id IS NULL

In this query I use LEFT JOIN to combine select1 and select2, and then only retain those rows from select1 that do not occur in select2.

Answer 2

In FB there is no minus operator, so :

select x1.fld from table x1 
where
not EXISTS(select x2.fld from table x2 where x2.fld=x1.fld)