* (star) clause
Applies to: Databricks SQL 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
-
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: Databricks SQL Databricks Runtime 11.3 LTS and above
Optionally prunes columns or fields from the referencable set of columns identified in the
select_star
clause.-
A column that is part of the set of columns that you can reference.
-
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 emptySTRUCT
.
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