search

dbeatty10 / dbt-mysql

dbt-mysql contains all of the code enabling dbt to work with MySQL and MariaDB
Apache License 2.0
75 stars 53 forks source link

Cross-database macros for the `intersect` and `except` operators #154

Open dbeatty10 opened 1 year ago

dbeatty10 commented 1 year ago

Describe the feature

Add cross-database macros for the intersect and except operators.

Additional context

These two operators are not available prior to MySQL 8.0.31.

The following query will return the version number of the MySQL server:

select version();

MySQL 8.0 Release Notes

Changes in MySQL 8.0.31 (2022-10-11, General Availability):

In this release MySQL adds support for the SQL standard INTERSECT and EXCEPT table operators.

query_a INTERSECT query_b includes only rows appearing in both result sets.

query_a EXCEPT query_b returns any rows from the result set of query_a which are not in the result of query_b.

INTERSECT and EXCEPT both support DISTINCT and ALL, with DISTINCT the default in both cases. (This is the same as for UNION).

INTERSECT groups before EXCEPT or UNION, so TABLE r EXCEPT TABLE s INTERSECT TABLE t is evaluated as TABLE r EXCEPT (TABLE s INTERSECT TABLE t).

See INTERSECT Clause, and EXCEPT Clause, for additional information and examples. (Bug #1309, Bug #31336, Bug #11747209, Bug #11744757, WL #349)