0

BigQuery supports the following notation for SQL:

select_list:
    { select_all | select_expression } [, ...]

select_all:
    [ expression. ]*
    [ EXCEPT ( column_name [, ...] ) ]
    [ REPLACE ( expression [ AS ] column_name [, ...] ) ]

Meaning something like the following can be done:

SELECT * EXCEPT (id, socialSecurity)

And some other small things.

Do any databases support this? I find the EXCEPT clause useful, and although I know how to use the REPLACE I've never found an actual practical use case for that ever. Are there ever any practical uses of that (i.e., aside from made up examples in the docs)?

David542
  • 104,438
  • 178
  • 489
  • 842
  • 1
    i think, there are plenty examples of practical use of `select * except/replace` here on SO for bigquery tag :o) as an example - i just recently answered question with `select * except` involved - https://stackoverflow.com/a/73513929/5221944 – Mikhail Berlyant Aug 27 '22 at 20:55
  • @MikhailBerlyant -- thanks, is that a BigQuery-specific extension, or have you seen that in any other databases? – David542 Aug 27 '22 at 21:01
  • 1
    just very few from quick search - https://stackoverflow.com/a/58837661/5221944, https://stackoverflow.com/a/42847416/5221944, https://stackoverflow.com/a/39297110/5221944 ... As of use in other databases - not sure :o( – Mikhail Berlyant Aug 27 '22 at 21:01
  • @MikhailBerlyant and yes I think `SELECT * EXCEPT` is great! I've just never had the use of using `* REPLACE` – David542 Aug 27 '22 at 21:02
  • What are you looking for in an answer? "Do any databases support this?", apparently bigquery does, at least. "Are there ever any practical uses of that", why does it matter? – HoneyBadger Aug 27 '22 at 21:56
  • @HoneyBadger right, do you know of any others that support it? That was my main question. – David542 Aug 27 '22 at 22:19

0 Answers0