FROM Clause (SQL Server Compact)

< table_source >

Specifies the tables and joined tables for the SELECT statement.

table_name [ [ AS ] table_alias]

Specifies the name of a table and an optional alias.

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.

For more information, see the SQL Server Books Online topics Using the FROM Clause and Using APPLY.

Examples

The following examples provide more information about using the FROM clause.

Copy

SELECT [Order ID], [Unit Price]
FROM [Order Details]

Using a Simple FROM Clause

The following example retrieves the Employee ID and Last Name columns from the Employees table in the Northwind sample database.

Copy

SELECT [Employee Id], [Last Name]
FROM Employees
ORDER BY [Employee Id]

Using a CROSS JOIN

The following example returns the cross product of the two tables, Employees and Orders. A list of all possible combinations of Employee ID rows and all Order ID rows are returned.

Copy

SELECT E.[Employee ID], O.[Order Id]
FROM Employees E
CROSS JOIN Orders O
ORDER BY E.[Employee ID], O.[Order Id];

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.

Copy

SELECT p.[Product Name], od.[Order ID]
FROM Products p
LEFT OUTER JOIN [Order Details] od
ON p.[Product ID] = od.[Product ID]
ORDER BY p.[Product Name];

Here is the result set:

Copy

        Product Name            Order ID
        --------------------------------
        Alice Mutton               10000
        Alice Mutton               10045
        Alice Mutton               10093
        Alice Mutton               10124
        Alice Mutton               10166
(2820 rows affected)

Using an INNER JOIN

The following example returns all product names and sales order IDs.

Copy

-- By default, SQL Server Compact performs an INNER JOIN if only  
-- the JOIN keyword is specified.
SELECT p.[Product Name], od.[Order ID]
FROM Products p
INNER JOIN [Order Details] od
ON p.[Product ID] = od.[Product ID]
ORDER BY p.[Product Name];

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.

Copy

SELECT p.[Product Name], od.[Order ID]
FROM Products p
RIGHT OUTER JOIN [Order Details] od
ON p.[Product ID] = od.[Product ID]
ORDER BY p.[Product Name];

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.

Copy

SELECT RTRIM(e.[First Name]) + ' ' + LTRIM(e.[Last Name]) AS [Employee Name], C.[Contact Name], C.City
FROM Employees e
INNER JOIN Orders O 
ON O.[Employee ID] = e.[Employee ID]
INNER JOIN (SELECT [Contact Name], City, [Customer Id] 
FROM Customers) AS C
ON O.[Customer Id] = C.[Customer Id]
ORDER BY e.[Last Name], e.[First Name];

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.

EXAMPLE:

Copy

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.

EXAMPLE:

Copy

SELECT
  *
FROM
  Customers AS C OUTER APPLY
  (
    SELECT TOP(1) *
    FROM Orders O
    WHERE O.[Customer ID] = C.[Customer ID]
    ORDER BY O.[Customer ID]
  ) AS topCustomerID

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.

Note
In SQL Server Compact, APPLY does not support user-defined functions.