Jonathan Boarman on 23 May 2024 22:15:42
Virtually all the key players in this space support this quality-of-life feature, including Databricks, Snowflake, BigQuery, DuckDB, and quite a few others.
The EXCEPT feature is a necessary feature to support any migration of existing SQL scripts. Significant re-architecture of existing SQL scripts would be necessary to migrate those projects into Fabric.
This is sometimes called "EXCLUDE", which is nice to avoid confusion between the other "EXCEPT" statement used for set operations similar to UNION queries, which is a very different concept.
References:
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_except (see: "EXCEPT")
https://docs.snowflake.com/en/sql-reference/sql/select (see: "EXCLUDE")
https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select.html (see: "EXCEPT")
https://stackoverflow.com/a/49760099/764307
- Comments (1)
RE: Support SELECT * EXCEPT(..) in Spark SQL -- Necessary Quality of Life Feature
-- select all referencable columns from all tables except t2.c4SELECT * EXCEPT(t2.c4) -- sometimes called "EXCLUDE", like in Snowflake and DuckDBFROMVALUES(1, 2) AS t1(c1, c2),VALUES(3, 4) AS t2(c3, c4);OUTPUT: 1 2 3