FROM#

Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements in Querona. In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).

Syntax#

[ FROM { <table_source> } [ ,...n ] ]
<table_source> ::=
{
    table_or_view_name [ [ AS ] table_alias ]
        [ WITH ( < table_hint > [ [ , ]...n ] ) ]
    | derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]
    | <joined_table>
    | @variable [ [ AS ] table_alias ]
    | @variable.function_call ( expression [ ,...n ] )
        [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
}

<joined_table> ::=
{
    <table_source> <join_type> <table_source> ON <search_condition>
    | <table_source> CROSS JOIN <table_source>
    | left_table_source { CROSS | OUTER } APPLY right_table_source
    | [ ( ] <joined_table> [ ) ]
}

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

<column_list> ::=
    column_name [ ,...n ]

Arguments#

<table_source>

Specifies a table, view, table variable, or derived table source, with or without an alias, to use in the SQL statement. Up to 256 table sources can be used in a statement, although the limit varies depending on available memory and the complexity of other expressions in the query. Individual queries may not support up to 256 table sources.

Note

Query performance may suffer with lots of tables referenced in a query. Compilation and optimization time is also affected by additional factors. These include the presence of indexes and indexed views on each <table_source> and the size of the <select_list> in the SELECT statement.

The order of table sources after the FROM keyword does not affect the result set that is returned. Querona returns errors when duplicate names appear in the FROM clause.

table_or_view_name

Is the name of a table or view.

If the table or view exists in another database on the same instance of Querona, use a fully qualified name in the form database.schema.object_name.

If the table or view exists outside the instance of Queronal, use a four-part name in the form linked_server.catalog.schema.object. For more information, see sp_addlinkedserver. A four-part name that is constructed by using the OPENDATASOURCE function as the server part of the name can also be used to specify the remote table source. When OPENDATASOURCE is specified, database_name and schema_name may not apply to all data sources and is subject to the capabilities of the OLE DB provider that accesses the remote object.

[AS] table_alias

Is an alias for table_source that can be used either for convenience or to distinguish a table or view in a self-join or subquery. An alias is frequently a shortened table name used to refer to specific columns of the tables in a join. If the same column name exists in more than one table in the join, Querona requires that the column name be qualified by a table name, view name, or alias. The table name cannot be used if an alias is defined.

When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_alias at the end of the clause is the associated table name for all columns, including grouping columns, returned.

WITH (<table_hint> ) Specifies that the query optimizer use an optimization or locking strategy with this table and for this statement. For more information, see Table Hints.

Joined table#

A joined table is a result set that is the product of two or more tables. For multiple joins, use parentheses to change the natural order of the joins.

Join type#

Specifies the type of join operation.

INNER Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. When no join type is specified, this is the default.

FULL [ OUTER ] Specifies that a row from either the left or right table that does not meet the join condition is included in the result set, and output columns that correspond to the other table are set to NULL. This is in addition to all rows typically returned by the INNER JOIN.

LEFT [ OUTER ] Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.

RIGHT [OUTER] Specifies all rows from the right table not meeting the join condition are included in the result set, and output columns that correspond to the other table are set to NULL, in addition to all rows returned by the inner join.

CROSS JOIN Specifies the cross-product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.

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 contains a table-valued function that takes column values from the left_table_source as one of its arguments.

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

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.

For more information, see the Remarks section.

left_table_source Is a table source as defined in the previous argument. For more information, see the Remarks section.

right_table_source Is a table source as defined in the previous argument. For more information, see the Remarks section.

Remarks#

The FROM clause supports the SQL-92-SQL syntax for joined tables and derived tables. SQL-92 syntax provides the INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS join operators.

UNION and JOIN within a FROM clause are supported within views and in derived tables and subqueries.

A self-join is a table that is joined to itself. Insert or update operations that are based on a self-join follow the order in the FROM clause.

Because Querona considers distribution and cardinality statistics from linked servers that provide column distribution statistics, the REMOTE join hint is not required to force evaluating a join remotely. The Querona query processor considers remote statistics and determines whether a remote-join strategy is appropriate.

Using APPLY#

Both the left and right operands of the APPLY operator are table expressions. The main difference between these operands is that the right_table_source can use a table-valued function that takes a column from the left_table_source as one of the arguments of the function. The left_table_source can include table-valued functions, but it cannot contain arguments that are columns from the right_table_source.

The APPLY operator works in the following way to produce the table source for the FROM clause:

Evaluates right_table_source against each row of the left_table_source to produce rowsets. The values in the right_table_source depend on left_table_source. right_table_source can be represented approximately this way: TVF(left_table_source.row), where TVF is a table-valued function.
Combines the result sets that are produced for each row in the evaluation of right_table_source with the left_table_source by performing a UNION ALL operation. The list of columns produced by the result of the APPLY operator is the set of columns from the left_table_source that is combined with the list of columns from the right_table_source.

Permissions#

Requires the permissions for the DELETE, SELECT, or UPDATE statement.

Examples#

A. Using a simple FROM clause#

The following example retrieves the TerritoryID and Name columns from the SalesTerritory table in the AdventureWorks2012 sample database.

SELECT TerritoryID, Name
FROM Sales.SalesTerritory
ORDER BY TerritoryID ;

Here is the result set:

TerritoryID Name
----------- ------------------------------
         Northwest
         Northeast
         Central
         Southwest
         Southeast
         Canada
         France
         Germany
         Australia
        United Kingdom
(10 row(s) affected)

B. Using the SQL-92 CROSS JOIN syntax#

The following example returns the cross product of the two tables Employee and Department in the AdventureWorks2012 database. A list of all possible combinations of BusinessEntityID rows and all Department name rows are returned.

SELECT e.BusinessEntityID, d.Name AS Department
  FROM HumanResources.Employee AS e
 CROSS JOIN HumanResources.Department AS d
 ORDER BY e.BusinessEntityID, d.Name ;

C. Using the SQL-92 FULL OUTER JOIN syntax#

The following example returns the product name and any corresponding sales orders in the SalesOrderDetail table in the AdventureWorks2012 database. It also returns any sales orders that have no product listed in the Product table, and any products with a sales order other than the one listed in the Product table.

-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name, sod.SalesOrderID
  FROM Production.Product AS p
  FULL OUTER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID
 ORDER BY p.Name ;

Using the SQL-92 LEFT OUTER JOIN syntax#

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

SELECT p.Name, sod.SalesOrderID
  FROM Production.Product AS p
  LEFT OUTER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID
 ORDER BY p.Name ;

Using the SQL-92 INNER JOIN syntax#

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

-- By default, |Product| performs an INNER JOIN if only the JOIN
-- keyword is specified.
SELECT p.Name, sod.SalesOrderID
  FROM Production.Product AS p
 INNER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID
 ORDER BY p.Name ;

Using the SQL-92 RIGHT OUTER JOIN syntax#

The following example joins two tables on TerritoryID and preserves the unmatched rows from the right table. The SalesTerritory table is matched with the SalesPerson table on the TerritoryID column in each table. All salespersons appear in the result set, whether or not they are assigned a territory.

SELECT st.Name AS Territory
     , sp.BusinessEntityID
  FROM Sales.SalesTerritory AS st
 RIGHT OUTER JOIN Sales.SalesPerson AS sp
    ON st.TerritoryID = sp.TerritoryID ;

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 cities in which they live.

SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name, d.City
FROM Person.Person AS p
INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
INNER JOIN
   (SELECT bea.BusinessEntityID, a.City
      FROM Person.Address AS a
     INNER
      JOIN Person.BusinessEntityAddress AS bea
        ON a.AddressID = bea.AddressID) AS d
        ON p.BusinessEntityID = d.BusinessEntityID
ORDER BY p.LastName, p.FirstName;

FROM

Contents