When a derived table, rowset, or table-valued function is used, the required table_alias at the end of the clause is the alternative table name for all columns, including grouping columns, that are returned.

< joined_table >

A result set that is the join of two or more tables.

For multiple joins, you can use parentheses to specify the order of the joins.

derived_table

A subquery that retrieves rows from the database. A derived_table is used as input to the outer query.

< join_type >

Specifies the type of join operation.

left_table_source { CROSS | OUTER } APPLY right_table_source

Specifies that the right_table_source of the APPLY operator is evaluated against every row of the left_table_source. This functionality is useful when the right_table_source depends on values from the left_table_source and there is no easy way to represent query using a join operation.

Either CROSS or OUTER must be specified with APPLY. When CROSS is specified and the right_table_source is evaluated against a specified row of the left_table_source, no rows are produced and an empty result set is returned.

When OUTER is specified, one row is produced for each row of the left_table_source, even when the right_table_source evaluates against that row and returns an empty result set.

left_table_source

A table source as defined in the previous argument. For more information, see the Remarks section at the end of this document.

right_table_source

A table source as defined in the previous argument. For more information, see the Remarks section at the end of this document.

CROSS JOIN

Specifies the cross product of two tables.

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 that do not meet 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 that do not meet 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.

JOIN

Indicates that the specified tables should be joined.

ON < search_condition >

Specifies the condition that the join is based on. The condition can specify any valid predicate, although columns and comparison operators are often used.

When the same query can be written by using a JOIN or APPLY, use JOIN because it can be faster.

If there are any table-references in the right_table_source that are not bound to any of the tables appearing in right_table_source, then it should either (i) match table names or table aliases that are exposed by the left_table_source or (ii) match a table name or alias that is exposed by an outer FROM clause (if the APPLY appears in a sub-query in the WHERE clause or the SELECT list). If there are matching references in i and ii, i takes precedence.

The APPLY operators have the same precedence as the JOIN operators. In the absence of parentheses, a sequence of JOIN and APPLY operators is evaluated from left to right.

Using a LEFT OUTER JOIN

The following example joins two tables on Product ID and preserves the unmatched rows from the left table. The Products table is matched with the Order Details table on the Product ID columns in each table. All products, ordered and not ordered, appear in the result set.

Using a RIGHT OUTER JOIN

The following example joins two tables on Product ID and preserves the unmatched rows from the right table. The Products table is matched with the Order Details table on the Product ID columns in each table. All products, ordered and not ordered, appear in the result set.

Using a Derived Table

The following example uses a derived table, a SELECT statement after the FROM clause, to return the first and last names of all employees and the contact names and cities of customers they are dealing with.

Using APPLY

CROSS APPLY specifies source data as the value of a table result instead of a table name. CROSS APPLY can be used in instances where a subset of data is generated on the fly for each row in the outer query. CROSS APPLY allows the user to specify the way a subset is constructed based upon the rows from the outer query. The following is an example of a query that performs an operation for each row using CROSS APPLY.

SELECT
*
FROM
Orders AS O CROSS APPLY
(
SELECT TOP(2) * --for each row in Orders there will be at most 2 customers
FROM Customers C
WHERE C.[Customer ID] = O.[Customer ID]
ORDER BY C.[Customer ID]
) AS theOrdersTopCustomers

OUTER APPLY specifies source data as the value of a table result instead of a table name. OUTER APPLY can be used in instances where a subset of data is generated on-the-fly for each row in the outer query. Unlike CROSS APPLY, OUTER APPLY also retains rows from the outer query that do not have any matches with the inner query. The following example shows a query that also includes results without a match.

The preceding example shows that the OUTER APPLY result includes the Customer ID, 'PARIS' because the Orders table does not contain any records for 'PARIS'. Therefore, the inner query returned NULL as the TOP Customer ID matching 'PARIS' in the Orders table. Changing OUTER with CROSS excludes 'PARIS' from the result.