Issue Details (XML | Word | Printable)

Key: CORE-808
Type: New Feature New Feature
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: alexkrav80
Votes: 29
Watchers: 10

If you were logged in you would be able to see more operations.
Firebird Core

Add support for INTERSECT and EXCEPT data set operators

Created: 29/Sep/04 12:00 AM   Updated: 28/Jun/18 08:26 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None

SF_ID: 1036767

 Description  « Hide
SFID: 1036767#
Submitted By: alexkrav80

Result of
(select ...)
(select ...)
should be intersection of results.

2) MINUS (also known as EXCEPT)
Result of
(select ...)
(select ...)
should be difference of results.

/*like in oracle*/

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alice F. Bird added a comment - 14/Jun/06 09:42 AM
Date: 2005-04-25 23:53
Sender: dimitr
Logged In: YES

They're named INTERSECT and EXCEPT in the SQL spec.

Philip Williams added a comment - 12/Aug/08 01:43 PM
SQL:1999 apparently also defines the variants "MINUS ALL" and "INTERSECT ALL", though I haven't seen them implemented much of anywhere. They would "not remove duplicates", which I assume means that A MINUS ALL B would still return rows from A even though they matched rows in B, as long as there were more matching rows in A than in B; in turn, INTERSECT ALL would return duplicate rows where matching rows were duplicated in both A and B; if they were duplicated only on one side, only one copy would be returned. But I don't have access to the actual spec, someone who does migth look into that.

Gerald Trost added a comment - 24/Jun/12 12:41 PM
The need for INTERSECT and EXCEPT (MINUS) was expressed in September 2004
I ask the product management to, please, specify wheather this request will be implemented or not.

Paul Beach added a comment - 24/Jun/12 12:49 PM - edited
Someone could choose if they wanted a feature badly enough to sponsor it...

Sean Leyne added a comment - 25/Apr/14 02:54 AM
Edited the subject to drop the "UNION" reference, as UNION was supported back in Interbase days.

(The presence of UNION made is seem that we didn't actually support the feature)

Lukas Eder added a comment - 10/Oct/14 12:39 PM
A related issue to the missing INTERSECT and EXCEPT keywords is the fact that currently, Firebird SQL doesn't allow for nesting set operations using parentheses as specified by the SQL standard. While UNION, UNION ALL, INTERSECT are associative operators among themselves, mixing them almost always requires parentheses for readability and disambiguation.

I have created an issue to request support for nesting set operations here:

Henner Kollmann added a comment - 26/Mar/18 02:02 PM
Are there any plans to support intersect in FB 3.0 or FB 4.0?

Karol Bieniaszewski added a comment - 19/Apr/18 09:03 PM - edited
in my humble opinion this feature is useless (maybe with ALL it have some function).
Only from marketing POV that FB have this sql spec supported..

I never seen in my life need for this and also do not seen any sample in real world system...

1. but what is the difference between
INTERSECT and INNER JOIN ? Only specification in join criteria...

2. EXCEPT vs LEFT JOIN with IS NOT NULL criteria of right table...

Volker Rehn added a comment - 19/Apr/18 11:57 PM
@Karol: while it is possible to get the same results by using join (haven't checked ALL), the point is semantics. You might as well argue that natural joins or CTE (without recursive) are useless. Intersect and except complement the union operator and provide for readable code. If you regularly go beyond plain select x from y where z - queries, those more abstract operators are welcome, and they also facilitate generated code.

Marketing/acceptance for projects is another valid point here, since Firebird is still underrated out there, You find comparison tables which just tick features.

Lukas Eder added a comment - 20/Apr/18 07:26 AM

INTERSECT can be used for null-safe comparisons between tuples. For instance EXISTS ((SELECT a, b, c FROM t1) INTERSECT (SELECT x, y, z FROM t2)) will check if there are any common elements in t1(a, b, c) and t2(x, y, z). This is significantly more difficult to do with JOIN.

EXCEPT can be used to compare two sets: NOT EXISTS (SELECT * FROM a EXCEPT SELECT * FROM b) AND NOT EXISTS (SELECT * FROM b EXCEPT SELECT * FROM a) can be handy for these kinds of things.

Surely, they're not the most important operators in SQL, but neither are FULL OUTER JOIN, RIGHT JOIN, NATURAL JOIN (I mean, really!), OFFSET. And yet Firebird supports these...