Edit

FROM - SELECT (Transact-SQL)

Applies to: SQL analytics endpoint in Microsoft Fabric and Warehouse in Microsoft Fabric

In Transact-SQL, a query can start with the FROM clause and place the SELECT clause after the table sources. This FROM-first syntax establishes the available tables and columns before defining the projection.

The trailing SELECT clause is optional. If you omit SELECT, the query behaves as if you specified SELECT *.

The following statements are equivalent:

FROM dbo.Employee;

FROM dbo.Employee
SELECT *;

FROM-first syntax doesn't change query results or execution behavior. It provides another way to author a query while preserving the semantics of an equivalent SELECT-first statement.

FROM-first syntax uses the table-source grammar supported by Fabric Data Warehouse and the SQL analytics endpoint.

Transact-SQL syntax conventions

Syntax

FROM { <table_source> [ ,...n ] }
[ SELECT <select_list> ]
[ WHERE <search_condition> ]
[ GROUP BY <group_by_expression> [ ,...n ] ]
[ HAVING <search_condition> ]
[ WINDOW <window_definition> [ ,...n ] ]
[ QUALIFY <search_condition> ]
[ ORDER BY <order_by_expression> [ ASC | DESC ] [ ,...n ] ]
[ FOR <JSON> ]

<table_source> ::=
{
    [ database_name . [ schema_name ] . | schema_name . ]
        table_or_view_name [ [ AS ] table_alias ]
    | built_in_table_valued_function [ [ AS ] table_alias ]
        [ ( column_alias [ ,...n ] ) ]
    | user_defined_table_valued_function [ [ AS ] table_alias ]
        [ ( column_alias [ ,...n ] ) ]
    | derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]
    | <joined_table>
    | <apply>
    | <pivoted_table>
    | <unpivoted_table>
}

Arguments

<table_source>

Specifies a table, view, built-in table-valued function, user-defined table-valued function, derived table, joined table, pivoted table, or unpivoted table, with or without an alias. Multiple table sources can be joined or separated by commas.

The order of table sources after the FROM keyword doesn't affect the result set. Duplicate names in the FROM clause produce an error. Duplicate unqualified column names can require table or alias qualification.

Queries that reference many table sources can require more compilation and optimization time. The practical number of table sources depends on available resources and query complexity.

table_or_view_name

The name of a table or view. Use a fully qualified name in the form database.schema.object_name when you reference an object in another database on the same SQL endpoint.

[ AS ] table_alias

An alias for a table source. Use an alias for convenience or to distinguish sources in joins, self-joins, and subqueries. After you define an alias, use the alias instead of the original table name to qualify columns. Derived tables, table-valued functions, PIVOT, and UNPIVOT can require an alias.

built_in_table_valued_function

Specifies a built-in function that returns a rowset. Supported table-valued functions include, but aren't limited to:

  • OPENJSON, which converts JSON text into rows and columns.
  • OPENROWSET(BULK...), which reads external files and returns their contents as rows.
  • STRING_SPLIT, which splits a delimited string into rows.
  • GENERATE_SERIES, which generates a numeric series.
  • sys.fn_helpcollations, which returns the collations supported by the SQL engine.

Built-in table-valued functions can define their own argument and output-column syntax. For example, OPENJSON can include a WITH clause that defines the returned columns.

user_defined_table_valued_function

Specifies a user-defined table-valued function. For syntax and behavior, see CREATE FUNCTION (Microsoft Fabric, Azure Synapse Analytics).

derived_table

A subquery that returns rows and acts as input to the outer query. A derived table requires a table alias. A table value constructor can also define a derived table.

column_alias

An optional alias that replaces a column name in the result of a derived table or table-valued function. If you specify a column alias list, provide an alias for every output column.

<joined_table>

Specifies a table source produced by joining two or more table sources. For complete join syntax and behavior, see FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL) and Joins.

For supported optimizer and data-movement options, see Join hints (Transact-SQL).

<apply>

Specifies a CROSS APPLY or OUTER APPLY table source. For its complete syntax and behavior, see Use APPLY.

<pivoted_table>

Specifies a table source transformed by the PIVOT operator. For its complete syntax and behavior, see Using PIVOT and UNPIVOT.

<unpivoted_table>

Specifies a table source transformed by the UNPIVOT operator. For its complete syntax and behavior, see Using PIVOT and UNPIVOT.

SELECT <select_list>

With FROM-first syntax, the SELECT clause is optional.

SELECT specifies the columns and expressions returned by the query. For complete projection syntax, see SELECT clause (Transact-SQL).

When present, the SELECT clause must appear immediately after all table sources, joins, and APPLY operators. If you omit the SELECT clause, <select_list> defaults to *.

FOR <JSON>

Specifies JSON formatting for the query result. For its complete syntax and behavior, see FOR clause (Transact-SQL).

Remarks

  • SELECT-first and FROM-first syntax are both supported, but you can't combine them in the same query block.
  • In FROM-first syntax, place SELECT immediately after the complete table-source expression, including any joins, APPLY, PIVOT, or UNPIVOT operators.
  • A query block can contain only one FROM clause and one optional trailing SELECT clause.
  • Omitting SELECT doesn't bypass existing restrictions on SELECT *, including restrictions in schema-bound objects.
  • FROM-first syntax can be used in top-level queries, common table expressions (CTEs), subqueries, views, inline table-valued functions, and supported INSERT query forms.
  • FROM-first syntax doesn't introduce a new SELECT INTO form.
  • FROM-first and equivalent SELECT-first queries return the same result and have the same runtime behavior.

Platform support

Both FROM-first and SELECT-first query syntaxes are supported in Fabric Data Warehouse and the SQL analytics endpoint.

FROM-first query syntax isn't supported in SQL Server, Azure SQL Database, Azure SQL Managed Instance, or SQL database in Fabric.

Examples

A. Specify SELECT after FROM

The following example declares the table source before the projected columns:

FROM dbo.Employee AS e
SELECT e.empId, e.name, e.dept;

This query is equivalent to:

SELECT e.empId, e.name, e.dept
FROM dbo.Employee AS e;

B. Omit SELECT

The following query omits the trailing SELECT clause:

FROM dbo.Employee;

The query is equivalent to:

SELECT *
FROM dbo.Employee;

C. Filter and group a FROM-first query

The following example declares the source, specifies the projection, and then filters and groups the rows:

FROM dbo.Employee AS e
SELECT e.dept, COUNT(*) AS EmployeeCount
WHERE e.managerId IS NOT NULL
GROUP BY e.dept;

D. Use joins before SELECT

The trailing SELECT clause appears after all table sources and join conditions:

FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
    ON soh.SalesOrderID = sod.SalesOrderID
SELECT
    soh.SalesOrderID,
    soh.OrderDate,
    sod.ProductID,
    sod.LineTotal;

E. Use FROM-first syntax in a CTE

The following example defines and consumes a CTE with FROM-first query blocks:

WITH managers AS (
    FROM dbo.Employee
    SELECT empId, name, dept
    WHERE managerId IS NOT NULL
)
FROM managers
SELECT empId, name, dept;

F. Use FROM-first syntax in an EXISTS subquery

The following example uses FROM-first syntax in both the outer query and the correlated subquery:

FROM dbo.Employee AS e
SELECT e.empId, e.name, e.dept
WHERE EXISTS (
    FROM dbo.Employee AS e2
    WHERE e2.dept = e.dept
      AND e2.empId <> e.empId
);

The omitted SELECT in the EXISTS subquery defaults to SELECT *. The EXISTS predicate tests only whether the subquery returns a row.

G. Use FROM-first syntax with INSERT

The following example inserts rows returned by a FROM-first query:

INSERT INTO dbo.EmployeeArchive
FROM dbo.Employee
SELECT empId, name, dept
WHERE dept = 'Accounting';

Related content

  • Last updated on