* (star) clause

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

A shorthand to name all the referencable columns in the FROM clause, or a specific table reference’s columns or fields in the FROM clause.

The list of columns or fields is ordered by the order of table references and the order of columns within each table reference. In case of fields it is ordered by the order of fields within the struct.

The _metadata column is not included this list. You must reference it explicitly.

Prior to Databricks Runtime 15.0 the star (*) clause can only be used in the SELECT list.

Syntax 

star_clause
   [ name . ] * [ except_clause ]

except_clause
   EXCEPT ( { column_name | field_name } [, ...] )

Parameters

  • name

    If name is a table name, lists the columns in the specified referenceable table. If name is a column or field name of type STRUCT`, lists the fields in the specified referenceable column or field. If not present lists all the columns from all referenceable tables in the FROM clause.

  • except_clause

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

    Optionally prunes columns or fields from the referencable set of columns identified in the select_star clause.

    • column_name

      A column that is part of the set of columns that you can reference.

    • field_name

      A reference to a field in a column of the set of columns that you can reference. If you exclude all fields from a STRUCT, the result is an empty STRUCT.

    Each name must reference a column included in the set of columns that you can reference or their fields. Otherwise, Databricks SQL raises a UNRESOLVED_COLUMN error. If names overlap or are not unique, Databricks SQL raises an EXCEPT_OVERLAPPING_COLUMNS error.

Examples 

– Return all columns in the FROM clause
> SELECT * FROM VALUES(1, 2) AS TA(c1, c2), VALUES(‘a’, b’) AS TB(ca, cb);
  1  2  a  b

– Return all columns from TA
> SELECT TA.* FROM VALUES(1, 2) AS TA(c1, c2), VALUES(‘a’, b’) AS TB(ca, cb);
  1  2

– Return all columns except TA.c1 and TB.cb
> SELECT * EXCEPT (c1, cb)  FROM VALUES(1, 2) AS TA(c1, c2), VALUES(‘a’, b’) AS TB(ca, cb);
  2  a

– Return all columns, but strip the field x from the struct.
> SELECT TA.* EXCEPT (c1.x) FROM VALUES(named_struct(‘x’, x, ‘y’, ‘y’), 2) AS (c1, c2), VALUES(‘a’, b’) AS TB(ca, cb);
  { y } 2 a b

-- Return all fields in c1.
> SELECT c1.* FROM VALUES(named_struct(‘x’, x, ‘y’, ‘y’), 2) AS (c1, c2), VALUES(‘a’, b’) AS TB(ca, cb);
  x  y

– Return the first not-NULL column in TA
> SELECT coalesce(TA.*)  FROM VALUES(1, 2) AS TA(c1, c2), VALUES(‘a’, b’) AS TB(ca, cb);
  1