Skip to content
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

Open
firebird-automations opened this issue Sep 29, 2004 · 17 comments
Open

Comments

@firebird-automations
Copy link
Collaborator

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*/

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10832 ] => Firebird [ 15268 ]

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @paulbeach

Someone could choose if they wanted a feature badly enough to sponsor it...

@firebird-automations
Copy link
Collaborator Author

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)

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

summary: UNION, INTERSECT and MINUS support => Add support for INTERSECT (ALL) and MINUS (ALL) data set operands

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

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*/

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Add support for INTERSECT and MINUS/EXCEPT data set operators => Add support for INTERSECT and EXCEPT data set operators

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Henner Kollmann (hkollmann)

Are there any plans to support intersect in FB 3.0 or FB 4.0?

@firebird-automations
Copy link
Collaborator Author

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...

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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...

@Bart76
Copy link

Bart76 commented Feb 15, 2022

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?...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants