Skip to content

Commit 98e7762

Browse files
BYHAMBYHAM
authored
FROM, metadata, escapes, format examples.
1 parent 210a4b2 commit 98e7762

1 file changed

Lines changed: 66 additions & 75 deletions

File tree

‎docs/t-sql/queries/from-transact-sql.md‎

Lines changed: 66 additions & 75 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,8 @@
1-
---
1+
---
22
title: "FROM (Transact-SQL) | Microsoft Docs"
33
ms.custom:
44
- "SQL2016_New_Updated"
5-
ms.date: "04/25/2017"
5+
ms.date: "06/28/2017"
66
ms.prod: "sql-non-specified"
77
ms.reviewer: ""
88
ms.suite: ""
@@ -120,7 +120,6 @@ manager: "jhubbard"
120120
121121
<end_date_time>::=
122122
<date_time_literal> | @date_time_variable
123-
124123
```
125124

126125
```
@@ -138,7 +137,8 @@ FROM { <table_source> [ ,...n ] }
138137
<joined_table> ::=
139138
{
140139
<table_source> <join_type> <table_source> ON search_condition
141-
| <table_source> CROSS JOIN <table_source> | left_table_source { CROSS | OUTER } APPLY right_table_source
140+
| <table_source> CROSS JOIN <table_source>
141+
| left_table_source { CROSS | OUTER } APPLY right_table_source
142142
| [ ( ] <joined_table> [ ) ]
143143
}
144144
@@ -152,11 +152,10 @@ FROM { <table_source> [ ,...n ] }
152152
REDUCE
153153
| REPLICATE
154154
| REDISTRIBUTE
155-
156155
```
157156

158157
## Arguments
159-
<table_source>
158+
\<table_source>
160159
Specifies a table, view, table variable, or derived table source, with or without an alias, to use in the [!INCLUDE[tsql](../../includes/tsql-md.md)] 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.
161160

162161
> [!NOTE]
@@ -176,26 +175,22 @@ FROM { <table_source> [ ,...n ] }
176175

177176
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.
178177

179-
WITH (<table_hint> )
178+
WITH (\<table_hint> )
180179
Specifies that the query optimizer use an optimization or locking strategy with this table and for this statement. For more information, see [Table Hints &#40;Transact-SQL&#41;](../../t-sql/queries/hints-transact-sql-table.md).
181180

182181
*rowset_function*
183182

184-
|&nbsp;|
185-
|:--|
186-
|**Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].|
187-
||
183+
**Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].
184+
188185

189186
Specifies one of the rowset functions, such as OPENROWSET, that returns an object that can be used instead of a table reference. For more information about a list of rowset functions, see [Rowset Functions &#40;Transact-SQL&#41;](../../t-sql/functions/rowset-functions-transact-sql.md).
190187

191188
Using the OPENROWSET and OPENQUERY functions to specify a remote object depends on the capabilities of the OLE DB provider that accesses the object.
192189

193190
*bulk_column_alias*
194191

195-
|&nbsp;|
196-
|:--|
197-
|**Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].|
198-
||
192+
**Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].
193+
199194

200195
Is an optional alias to replace a column name in the result set. Column aliases are allowed only in SELECT statements that use the OPENROWSET function with the BULK option. When you use *bulk_column_alias*, specify an alias for every table column in the same order as the columns in the file.
201196

@@ -207,10 +202,8 @@ FROM { <table_source> [ ,...n ] }
207202

208203
OPENXML <openxml_clause>
209204

210-
|&nbsp;|
211-
|:--|
212-
|**Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].|
213-
||
205+
**Applies to**: [!INCLUDE[ssKatmai](../../includes/sskatmai-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].
206+
214207

215208
Provides a rowset view over an XML document. For more information, see [OPENXML &#40;Transact-SQL&#41;](../../t-sql/functions/openxml-transact-sql.md).
216209

@@ -224,14 +217,12 @@ FROM { <table_source> [ ,...n ] }
224217

225218
*table_or_view_name* FOR SYSTEM_TIME <system_time>
226219

227-
|&nbsp;|
228-
|:--|
229-
|**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].|
230-
||
220+
**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].
221+
231222

232223
Specifies that a specific version of data is returned from the specified temporal table and its linked system-versioned history table
233224

234-
<tablesample_clause>
225+
\<tablesample_clause>
235226
Specifies that a sample of data from the table is returned. The sample may be approximate. This clause can be used on any primary or joined table in a SELECT, UPDATE, or DELETE statement. TABLESAMPLE cannot be specified with views.
236227

237228
> [!NOTE]
@@ -258,7 +249,7 @@ FROM { <table_source> [ ,...n ] }
258249
<joined_table>
259250
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.
260251

261-
<join_type>
252+
\<join_type>
262253
Specifies the type of join operation.
263254

264255
**INNER**
@@ -273,7 +264,7 @@ FROM { <table_source> [ ,...n ] }
273264
RIGHT [OUTER]
274265
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.
275266

276-
<join_hint>
267+
\<join_hint>
277268
For [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] and [!INCLUDE[ssSDS](../../includes/sssds-md.md)], specifies that the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] query optimizer use one join hint, or execution algorithm, per join specified in the query FROM clause. For more information, see [Join Hints &#40;Transact-SQL&#41;](../../t-sql/queries/hints-transact-sql-join.md).
278269

279270
For [!INCLUDE[ssSDW](../../includes/sssdw-md.md)] and [!INCLUDE[ssPDW](../../includes/sspdw-md.md)], these join hints apply to INNER joins on two distribution incompatible columns. They can improve query performance by restricting the amount of data movement that occurs during query processing. The allowable join hints for [!INCLUDE[ssSDW](../../includes/sssdw-md.md)] and [!INCLUDE[ssPDW](../../includes/sspdw-md.md)] are as follows:
@@ -290,7 +281,7 @@ FROM { <table_source> [ ,...n ] }
290281
JOIN
291282
Indicates that the specified join operation should occur between the specified table sources or views.
292283

293-
ON <search_condition>
284+
ON \<search_condition>
294285
Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are frequently used, for example:
295286

296287
```tsql
@@ -356,39 +347,30 @@ ON (p.ProductID = v.ProductID);
356347
UNPIVOT < unpivot_clause >
357348
Specifies that the input table is narrowed from multiple columns in *column_list* into a single column called *pivot_column*. For more information about PIVOT and UNPIVOT, see [Using PIVOT and UNPIVOT](../../t-sql/queries/from-using-pivot-and-unpivot.md).
358349

359-
AS OF <date_time>
350+
AS OF \<date_time>
351+
352+
**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].
360353

361-
|&nbsp;|
362-
|:--|
363-
|**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].|
364-
||
365354

366355
Returns a table with single record for each row containing the values that were actual (current) at the specified point in time in the past. Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the *<date_time>* parameter. The value for a row is deemed valid if the *system_start_time_column_name* value is less than or equal to the *<date_time>* parameter value and the *system_end_time_column_name* value is greater than the *<date_time>* parameter value.
367356

368-
FROM <start_date_time> TO <end_date_time>
357+
FROM \<start_date_time> TO \<end_date_time>
358+
359+
**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].
369360

370-
|&nbsp;|
371-
|:--|
372-
|**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].|
373-
||
374361

375-
Returns a table with the values for all record versions that were active within the specified time range, regardless of whether they started being active before the *<start_date_time>* parameter value for the FROM argument or ceased being active after the *<end_date_time>* parameter value for the TO argument. Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. Rows that became active exactly on the lower boundary defined by the FROM endpoint are included and rows that became active exactly on the upper boundary defined by the TO endpoint are not included.
362+
Returns a table with the values for all record versions that were active within the specified time range, regardless of whether they started being active before the *\<start_date_time>* parameter value for the FROM argument or ceased being active after the *\<end_date_time>* parameter value for the TO argument. Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. Rows that became active exactly on the lower boundary defined by the FROM endpoint are included and rows that became active exactly on the upper boundary defined by the TO endpoint are not included.
376363

377-
BETWEEN <start_date_time> AND <end_date_time>
364+
BETWEEN \<start_date_time> AND \<end_date_time>
378365

379-
|&nbsp;|
380-
|:--|
381-
|**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].|
382-
||
366+
**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].
383367

384-
Same as above in the **FROM <start_date_time> TO <end_date_time>** description, except it includes rows that became active on the upper boundary defined by the <end_date_time> endpoint.
368+
Same as above in the **FROM \<start_date_time> TO \<end_date_time>** description, except it includes rows that became active on the upper boundary defined by the \<end_date_time> endpoint.
385369

386-
CONTAINED IN (<start_date_time> , <end_date_time>)
370+
CONTAINED IN (\<start_date_time> , \<end_date_time>)
371+
372+
**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].
387373

388-
|&nbsp;|
389-
|:--|
390-
|**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].|
391-
||
392374

393375
Returns a table with the values for all record versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.
394376

@@ -615,10 +597,8 @@ GO
615597

616598
### M. Using FOR SYSTEM_TIME
617599

618-
|&nbsp;|
619-
|:--|
620-
|**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].|
621-
||
600+
**Applies to**: [!INCLUDE[ssSQL15](../../includes/sssql15-md.md)] through [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] and [!INCLUDE[sqldbesa](../../includes/sqldbesa-md.md)].
601+
622602
The following example uses the FOR SYSTEM_TIME AS OF date_time_literal_or_variable argument to return table rows that were actual (current) as of January 1, 2014.
623603

624604
```tsql
@@ -702,8 +682,9 @@ ORDER BY SalesTerritoryKey;
702682
-- Uses AdventureWorks
703683
704684
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
705-
FROM FactInternetSales AS fis INNER JOIN DimProduct AS dp
706-
ON dp.ProductKey = fis.ProductKey;
685+
FROM FactInternetSales AS fis
686+
INNER JOIN DimProduct AS dp
687+
ON dp.ProductKey = fis.ProductKey;
707688
```
708689

709690
Since the `INNER` keyword is not required for inner joins, this same query could be written as:
@@ -712,7 +693,8 @@ ON dp.ProductKey = fis.ProductKey;
712693
-- Uses AdventureWorks
713694
714695
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
715-
FROM FactInternetSales fis JOIN DimProduct dp
696+
FROM FactInternetSales AS fis
697+
JOIN DimProduct AS dp
716698
ON dp.ProductKey = fis.ProductKey;
717699
```
718700

@@ -722,8 +704,9 @@ ON dp.ProductKey = fis.ProductKey;
722704
-- Uses AdventureWorks
723705
724706
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
725-
FROM FactInternetSales AS fis JOIN DimProduct AS dp
726-
ON dp.ProductKey = fis.ProductKey
707+
FROM FactInternetSales AS fis
708+
JOIN DimProduct AS dp
709+
ON dp.ProductKey = fis.ProductKey
727710
WHERE fis.SalesOrderNumber > 'SO50000'
728711
ORDER BY fis.SalesOrderNumber;
729712
```
@@ -735,8 +718,9 @@ ORDER BY fis.SalesOrderNumber;
735718
-- Uses AdventureWorks
736719
737720
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
738-
FROM FactInternetSales AS fis LEFT OUTER JOIN DimProduct AS dp
739-
ON dp.ProductKey = fis.ProductKey;
721+
FROM FactInternetSales AS fis
722+
LEFT OUTER JOIN DimProduct AS dp
723+
ON dp.ProductKey = fis.ProductKey;
740724
```
741725

742726
This query could also be written without the `OUTER` keyword.
@@ -747,8 +731,9 @@ ON dp.ProductKey = fis.ProductKey;
747731
-- Uses AdventureWorks
748732
749733
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
750-
FROM DimProduct AS dp RIGHT OUTER JOIN FactInternetSales AS fis
751-
ON dp.ProductKey = fis.ProductKey;
734+
FROM DimProduct AS dp
735+
RIGHT OUTER JOIN FactInternetSales AS fis
736+
ON dp.ProductKey = fis.ProductKey;
752737
```
753738

754739
The following query uses the `DimSalesTerritory` table as the left table in a left outer join. It retrieves the `SalesOrderNumber` values from the `FactInternetSales` table. If there are no orders for a particular `SalesTerritoryKey`, the query will return a NULL for the `SalesOrderNumber` for that row. This query is ordered by the `SalesOrderNumber` column, so that any NULLs in this column will appear at the top of the results.
@@ -757,8 +742,9 @@ ON dp.ProductKey = fis.ProductKey;
757742
-- Uses AdventureWorks
758743
759744
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber
760-
FROM DimSalesTerritory AS dst LEFT OUTER JOIN FactInternetSales AS fis
761-
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
745+
FROM DimSalesTerritory AS dst
746+
LEFT OUTER JOIN FactInternetSales AS fis
747+
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
762748
ORDER BY fis.SalesOrderNumber;
763749
```
764750

@@ -768,8 +754,9 @@ ORDER BY fis.SalesOrderNumber;
768754
-- Uses AdventureWorks
769755
770756
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber
771-
FROM FactInternetSales AS fis RIGHT OUTER JOIN DimSalesTerritory AS dst
772-
ON fis.SalesTerritoryKey = dst.SalesTerritoryKey
757+
FROM FactInternetSales AS fis
758+
RIGHT OUTER JOIN DimSalesTerritory AS dst
759+
ON fis.SalesTerritoryKey = dst.SalesTerritoryKey
773760
ORDER BY fis.SalesOrderNumber;
774761
```
775762

@@ -780,8 +767,9 @@ ORDER BY fis.SalesOrderNumber;
780767
-- Uses AdventureWorks
781768
782769
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber
783-
FROM DimSalesTerritory AS dst FULL OUTER JOIN FactInternetSales AS fis
784-
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
770+
FROM DimSalesTerritory AS dst
771+
FULL OUTER JOIN FactInternetSales AS fis
772+
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
785773
ORDER BY fis.SalesOrderNumber;
786774
```
787775

@@ -791,8 +779,9 @@ ORDER BY fis.SalesOrderNumber;
791779
-- Uses AdventureWorks
792780
793781
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber
794-
FROM DimSalesTerritory AS dst FULL JOIN FactInternetSales AS fis
795-
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
782+
FROM DimSalesTerritory AS dst
783+
FULL JOIN FactInternetSales AS fis
784+
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
796785
ORDER BY fis.SalesOrderNumber;
797786
```
798787

@@ -803,7 +792,8 @@ ORDER BY fis.SalesOrderNumber;
803792
-- Uses AdventureWorks
804793
805794
SELECT dst.SalesTerritoryKey, fis.SalesOrderNumber
806-
FROM DimSalesTerritory AS dst CROSS JOIN FactInternetSales AS fis
795+
FROM DimSalesTerritory AS dst
796+
CROSS JOIN FactInternetSales AS fis
807797
ORDER BY fis.SalesOrderNumber;
808798
```
809799

@@ -832,7 +822,7 @@ FROM
832822
(SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
833823
FROM DimProduct AS dp
834824
INNER REDUCE JOIN FactInternetSales AS fis
835-
ON dp.ProductKey = fis.ProductKey
825+
ON dp.ProductKey = fis.ProductKey
836826
) AS dTable
837827
ORDER BY SalesOrderNumber;
838828
```
@@ -848,7 +838,7 @@ FROM
848838
(SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
849839
FROM DimProduct AS dp
850840
INNER REPLICATE JOIN FactInternetSales AS fis
851-
ON dp.ProductKey = fis.ProductKey
841+
ON dp.ProductKey = fis.ProductKey
852842
) AS dTable
853843
ORDER BY SalesOrderNumber;
854844
```
@@ -863,8 +853,9 @@ ORDER BY SalesOrderNumber;
863853
864854
EXPLAIN
865855
SELECT dp.ProductKey, fis.SalesOrderNumber, fis.TotalProductCost
866-
FROM DimProduct dp INNER REDISTRIBUTE JOIN FactInternetSales fis
867-
ON dp.ProductKey = fis.ProductKey;
856+
FROM DimProduct AS dp
857+
INNER REDISTRIBUTE JOIN FactInternetSales AS fis
858+
ON dp.ProductKey = fis.ProductKey;
868859
```
869860

870861
## See Also

0 commit comments

Comments
 (0)