Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]]

[Miles Elam <miles.elam@xxxxxxxxxxxxxx>]

On Tue, Feb 25, 2020 at 6:41 AM Josef Šimánek <josef.simanek@xxxxxxxxx> wrote:

út 25. 2. 2020 v 15:35 odesílatel Miles Elam <miles.elam@xxxxxxxxxxxxxx> napsal:

How do you see this syntax working in a JOIN query?

SELECT x.* EXCEPT x.col1, x.col2, y.col1

FROM tablex AS x

  LEFT JOIN tabley AS y;

The column(s) you want to exclude become ambiguous.

Can you explain how are those column(s) ambiguous in your example? I would expect to select everything from table x (as SELECT x.* should do) except x.col1 and x.col2. Nothing is selected from table y thus y.col1 is not relevant here (the question is if this is problem or not - raise, ignore?).

Do you mean 

  "select everything from tablex except for tablex.col1, and also select tablex.col2 and tabley.col1"

or

  "select everything from tablex except for tablex.col1 AND tablex.col2, and also select tabley.col1"

?

It's entirely possible to specify a column twice. It's quite common for me to see what fields I need from a table by doing a "SELECT * ... LIMIT 1" and then "SELECT col1, * ... LIMIT 1" as I refine the query, eventually eliminating the wildcard when I'm done. (When I'm using an IDE that doesn't support SQL table/column autocomplete.)

EXCEPT would need to be scoped as to which columns it's meant to be excluding without ambiguity. Just reading from the column list until you hit another table's columns or a function strikes me as far too loose.