Support for set operators: Intersect, Union, Except, in TypeOrm

[firmbase-tal]

Feature Description

The Problem

I would like for support to be added for the INTERSECT, UNION, and EXCEPT operators for Postgres/MySQL.
Unfortunately, there is currently no way to build such queries, without using raw queries.
These operators are very situationally useful, and would enhance querying capabilities for TypeORM.

The Solution

I think the best way to implement the change would be to extend the functionality of the SelectQueryBuilder to include these operators as functions:

for example, for given sql:

select id as t1_id from table1
INTERSECT
select table1_id as t1_id from table2

either:

query1 = manager.createQueryBuilder().select('id', 't1_id').from('table1');
query2 = manager.createQueryBuilder().select('table1_id', 't1_id').from('table2');
query = manager.createQueryBuilder().intersect([query1, query2])

or:

query = manager.createQueryBuilder().select('id', 't1_id').from('table1').intersect((subQuery) => subQuery.select('table1_id', 't1_id').from('table2'))

Considered Alternatives

For INTERSECT, I considered combining inner joins with distinct.
For UNIONS, I created two different queries, and appended their results in the back end.
For EXCEPT, I have yet to encounter a use case while working with TypeORM, therefore I have no good idea for a workaround.

There is a workaround that I picked up from a related issue from 2018, which is linked as additional context.

Additional Context

The feature has been partially requested and closed in the past, without explanation about the rejection: #2992

Relevant Database Driver(s)

DB Type	Relevant
aurora-data-api	no
aurora-data-api-pg	no
better-sqlite3	no
cockroachdb	no
cordova	no
expo	no
mongodb	no
mysql	yes
nativescript	no
oracle	yes
postgres	yes
react-native	no
sap	no
sqlite	yes
sqlite-abstract	no
sqljs	no
sqlserver	no

Are you willing to resolve this issue by submitting a Pull Request?

• ✅ Yes, I have the time, and I know how to start.
• ✖️ Yes, I have the time, but I don't know how to start. I would need guidance.
• ✖️ No, I don’t have the time, but I can support (using donations) development.
• ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[Ginden]

This is blocked by #8466

It can be workarounded using CTEs after merging #8534

[alumni]

The full syntax for set operators is <UNION | EXCEPT | INTERSECT> [ALL | DISTINCT]. DISTINCT is implicit in most dialects, but ALL needs to be explicitly added if you don't want to filter duplicate results. Most dialects only support UNION ALL (MS SqlServer, Oracle, SAP HANA, Sqlite don't have EXCEPT ALL or INTERSECT ALL in their docs), while some dialects use MINUS instead of EXCEPT (Oracle only?)

MariaDB:

• https://mariadb.com/kb/en/union/
• https://mariadb.com/kb/en/except/
• https://mariadb.com/kb/en/intersect/

MS SqlServer

• https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver16
• https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-ver16

Oracle

• https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/The-UNION-ALL-INTERSECT-MINUS-Operators.html#GUID-B64FE747-586E-4513-945F-80CB197125EE

Postgres:

• https://www.postgresql.org/docs/current/queries-union.html

SAP HANA:

• https://help.sap.com/docs/HANA_CLOUD_DATABASE/c1d3f60099654ecfb3fe36ac93c121bb/20a380977519101494ceddd944e87527.html#loio20a380977519101494ceddd944e87527___esql_operators_1sql_operators_set_operators
• https://help.sap.com/docs/HANA_CLOUD_DATABASE/c1d3f60099654ecfb3fe36ac93c121bb/20fcf24075191014a89e9dc7b8408b26.html#loio20fcf24075191014a89e9dc7b8408b26__set_operator

Sqlite

• https://www.sqlitetutorial.net/sqlite-union/
• https://www.sqlitetutorial.net/sqlite-except/
• https://www.sqlitetutorial.net/sqlite-intersect/

[john21323]

Is there anything new here? 😳😳😳