New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Add support for INTERSECT and EXCEPT data set operators [CORE808] #1194
Comments
Commented by: Alice F. Bird (firebirds) Date: 2005-04-25 23:53 They're named INTERSECT and EXCEPT in the SQL spec. |
Modified by: @pcisarWorkflow: 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 |
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# 1) intersect: 2) minus /*like in oracle*/ => SFID: 1036767# 1) INTERSECT: 2) MINUS (also known as EXCEPT) /*like in oracle*/ |
Modified by: @dyemanovsummary: 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). 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 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) 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?... |
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*/
The text was updated successfully, but these errors were encountered: