I am wondering if the SQL standard says anything about including table columns along with * in select. For example:

select *, id from users limit 10;

Will the above query return two columns both name id or will they be deduped? I tried with PostgreSQL and MySQL and both return both of the columns. Is this a part of the standard?

CodePudding user response：

SQL does not allow any column to be added with the * symbol. Already all the columns are retrieved from * then there is no need for defining a separate column for it.

CodePudding user response：

* is a palceholder and is expanded to all columns. This happens irrespectively of any specifically named columns which you can also do in addition to select *.

select *, *
from table

In some RDBMS this will expand the first * into all columns, and then as instructed, the second * also. Of course, this is not useful but not prohibited in SQL Server, Postgress, SqlLite and a few others.

select *
from a join b on a.id=b.id

will select all columns from both tables.

More usefully,

select a.*, b.col
from a join b on a.id=b.id

Will return all columns from table a and only column col from table b

Generally, * is a shortcut and makes it easy to return data for ad-hoc queries but should be avoided in production code where it's best practice to return only the columns required, it also helps the compilation phase by skipping the automatic expansion of all columns.

It should be avoided for view definitions where the expansion is fixed when the view is created, leaving changes to the referenced table's schema a possible cause of a runtime error.

select * is fine to use for any exists clause since it's not expanded at all and completely ignored.