FROM Clause

Transact-SQL Reference

previous page next page

Transact-SQL Reference

FROM Clause

Specifies the table(s) from which to retrieve rows. The FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names). For more information, see FROM.

Syntax

[ FROM { < table_source > } [ ,...n ] ]

< table_source > ::=
    table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
    | view_name [ [ AS ] table_alias ]
    | rowset_function [ [ AS ] table_alias ]
    | OPENXML
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
    | < joined_table >

< joined_table > ::=
     < table_source > < join_type > < table_source > ON < search_condition >
    | < table_source > CROSS JOIN < table_source >
    | < joined_table >

< join_type > ::=
    [ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ]
    [ < join_hint > ]
    JOIN

Arguments

< table_source >

Specifies tables, views, derived tables, and joined tables for the SELECT statement.

table_name [ [ AS ] table_alias ]: Specifies the name of a table and an optional alias.
view_name [ [ AS ] table_alias ]: Specifies the name, a view, and an optional alias.
rowset_function [ [ AS ] table_alias ]: Is the name of a rowset function and an optional alias. For more information about a list of rowset functions, see Rowset Functions.
OPENXML: Provides rowset view over an XML document. For more information see OPENXML
WITH ( < table_hint > [ ,...n ] ): Specifies one or more table hints. For more information about table hints, see FROM.
derived_table [ [ AS ] table_alias ]: Is a nested SELECT statement, retrieving rows from the specified database and table(s).
column_alias: Is an optional alias to replace a column name in the result set.

< joined_table >

Is a result set that is the product of two or more tables. For example:

SELECT *
FROM tab1 LEFT OUTER JOIN tab2 ON tab1.c3 = tab2.c3
    RIGHT OUTER JOIN tab3 LEFT OUTER JOIN tab4
        ON tab3.c1 = tab4.c1
        ON tab2.c3 = tab4.c3

For multiple CROSS joins, use parentheses to change the natural order of the joins.

< join_type >

Specifies the type of join operation.

INNER: Specifies that all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.
LEFT [ OUTER ]: Specifies that all rows from the left table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the left table are set to NULL.
RIGHT [ OUTER ]: Specifies that all rows from the right table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the right table are set to NULL.
FULL [ OUTER ]: If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.
< join_hint >: Specifies a join hint or execution algorithm. If <join_hint> is specified, INNER, LEFT, RIGHT, or FULL must also be explicitly specified. For more information about join hints, see FROM.
JOIN: Indicates that the specified tables or views should be joined.

ON < search_condition >

Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are often used. For example:

SELECT ProductID, Suppliers.SupplierID
    FROM Suppliers JOIN Products 
    ON (Suppliers.SupplierID = Products.SupplierID)

When the condition specifies columns, the columns do not have to have the same name or same data type. However, if the data types are not identical, they must be either compatible or types that Microsoft® SQL Server™ can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type using the CAST function.

For more information about search conditions and predicates, see Search Condition.

CROSS JOIN

Specifies the cross-product of two tables. Returns the same rows as if the tables to be joined were simply listed in the FROM clause and no WHERE clause was specified. For example, both of these queries return a result set that is a cross join of all the rows in T1 and T2:

SELECT * FROM T1, T2
SELECT * FROM T1 CROSS JOIN T2

previous page start next page