firebird
firebird copied to clipboard
FirebirdSQL
Add support for INTERSECT and EXCEPT data set operators [CORE808]
Submitted by: alexkrav80 (alexkrav80)
Votes: 30
SFID: 1036767# Submitted By: alexkrav80
1) INTERSECT: Result of (select ...) INTERSECT (select ...) should be intersection of results.
2) MINUS (also known as EXCEPT) Result of (select ...) MINUS (select ...) should be difference of results.
/*like in oracle*/
Commented by: Alice F. Bird (firebirds)
Date: 2005-04-25 23:53 Sender: dimitr Logged In: YES user_id=61270
They're named INTERSECT and EXCEPT in the SQL spec.
Modified by: @pcisar
Workflow: jira [ 10832 ] => Firebird [ 15268 ]
Commented by: Philip Williams (unordained)
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.
Commented by: Gerald Trost (geraldtrost)
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.
Commented by: @paulbeach
Someone could choose if they wanted a feature badly enough to sponsor it...
Commented by: Sean Leyne (seanleyne)
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)
Modified by: Sean Leyne (seanleyne)
summary: UNION, INTERSECT and MINUS support => Add support for INTERSECT (ALL) and MINUS (ALL) data set operands
Modified by: Sean Leyne (seanleyne)
summary: Add support for INTERSECT (ALL) and MINUS (ALL) data set operands => Add support for INTERSECT and MINUS/EXCEPT data set operands
Modified by: Sean Leyne (seanleyne)
summary: Add support for INTERSECT and MINUS/EXCEPT data set operands => Add support for INTERSECT and MINUS/EXCEPT data set operators
Modified by: Sean Leyne (seanleyne)
description: SFID: 1036767# Submitted By: alexkrav80
1) intersect: Result of (select ...) intersect (select ...) should be intersection of results.
2) minus Result of (select ...) minus (select ...) should be difference of results.
/*like in oracle*/
=>
SFID: 1036767# Submitted By: alexkrav80
1) INTERSECT: Result of (select ...) INTERSECT (select ...) should be intersection of results.
2) MINUS (also known as EXCEPT) Result of (select ...) MINUS (select ...) should be difference of results.
/*like in oracle*/
Modified by: @dyemanov
summary: Add support for INTERSECT and MINUS/EXCEPT data set operators => Add support for INTERSECT and EXCEPT data set operators
Commented by: Lukas Eder (lukas.eder)
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: CORE4577
Commented by: Henner Kollmann (hkollmann)
Are there any plans to support intersect in FB 3.0 or FB 4.0?
Commented by: @livius2
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...
Commented by: Volker Rehn (vr2_s18)
@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.
Commented by: Lukas Eder (lukas.eder)
@Karol:
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...
Having issues like these open for such a long time makes me wonder... Are these features really low-prio? Do they really need to be 'sponsored' to make it into a release? Is it really true that nobody seems to actually need them? Or does everybody who do need such features simply switch to another DBMS?...
