Data Module > Import & export > Data mappings > SQL Basics > Keywords

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).

Syntax

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

< table_source > ::=

table_name [ [ AS ] table_alias ]

| < joined_table >

< joined_table > ::=

< table_source > < join_type > < table_source > ON < search_condition >

| < table_source > CROSS JOIN < table_source >

| < joined_table >

| <Pivoted Table>

| <Unpivoted Table>

| <OpenRowSet>

< join_type > ::=

[ INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } ] JOIN

<Pivoted Table> ::= <table source> PIVOT <pivot_clause> <alias>

<pivot_clause> ::= '(' AggregateFunction '(' ValueColumnName ')' FOR PivotColumnName IN '(' <column list> ')' ')'

| '(' AggregateFunction '(' ValueColumnName ')' FOR PivotColumnName ')'

| '(' AggregateFunction '(' ValueColumnName ')' FOR PivotColumnName IN '(' <Sub Query> ')' ')'

<Unpivoted Table> ::= <table source> UNPIVOT <unpivot_clause> <alias>

<unpivot_clause> ::= '(' Identifier FOR Identifier IN '(' <column list> ')' ')'

<OpenRowSet> ::= OPENROWSET '(' <source> ',' {table_name | query} ')' [As table_alias]

<source> ::= provider_name ',' connection_string

| registered_runtime_connection_alias

Arguments

< table_source >

Specifies tables and joined tables for the SELECT statement.

table_name [ [ AS ] table_alias ]

Specifies the name of a table and an optional alias.

< 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

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 *

FROM tab1 JOIN tab2

ON tab1.c1 = tab2.c2

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 .NET 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

PIVOT <pivot_clause>

Specifies that the table_source is pivoted based on the PivotColumnName. table_source is a table or sub-query. The output is a table that contains all columns of the table_source except the pivot_column and value_column. PIVOT performs a grouping operation on the input table with regard to the value and pivot columns and returns one row for each group.

The output contains one column for each value specified in the column_list that appears in the pivot_column of the input_table.

AggregateFunction

One of the standard COUNT, AVG, SUM, MIN, MAX, COUNT, STDEV, or VAR aggregate functions.

ValueColumnName

Is the value column of the PIVOT operator. When used with UNPIVOT, this column cannot be the name of an existing column in the input table_source.

FOR PivotColumnName

For PIVOT, this column must be of a type implicitly convertible to a string.

For UNPIVOT, this is the name of the output column that becomes narrowed from the table_source. There cannot be an existing column in table_source with that name.

IN ( column_list )

For PIVOT, this lists the values in the pivot_column that will become the column names of the output table. The list cannot specify any column names that already exist in the input table_source that is being pivoted. When ommitted, each distinct value encountered in the pivot_column will produce a new column in the output table.

In the UNPIVOT clause, lists the columns in table_source that will be narrowed into a single pivot_column.

IN ( Sub Query )

For PIVOT, a sub-query returns the list of values in the pivot_column that will become the column names of the output table. A DISTINCT list of the result-set is implicitly generated from the sub-query. A result from the sub-query must be of a type that is implicitly convertible to a string.

UNPIVOT < unpivot_clause >

Specifies that the input table is narrowed from multiple columns in column_list into a single column called pivot_column.

OPENROWSET

Specified a .NET Provider, database connection and query that can be used as a table source. Use it for joining data in a DataSet with data in an external database. This is useful when part of the data is too large or practical to cache in a DataSet. It is also useful when the underlying database does not support a feature of QueryADataset such as PIVOT/UNPIVOT.

Openrowset has two options for connecting to a database. When a provider_name and connection_string is specified, QueryADataset will load the provider and open the connection. An alternative is to register an existing open connection and provide a registered_runtime_connection_alias name. For example:

Dim cn As New SqlConnection(ConnectionString)

cn.Open()

DsCommand.Registry.DbConnections.Add("myconnection", cn)

Dim sql As String = "select * from openrowset('myconnection', 'select * from titles')"

DsCommand.Execute(sql, Nothing)

The provider_name is the invariant name of a .NET data provider. These can be found in machine.config. The following are some examples:

</DbProviderFactories>

connection_string is the .NET provider connection string to the database. It is provider specific, but usually contains properties for Data Source, Initial Catalog, Integrated Security or User Name and password.

OpenRowSet accepts either a table_name or SELECT query. When table_name is specified, all the rows and columns of the table are returned as a DataTable. Note that the underlying provider (such as Access) must support the CommandType.TableDirect feature, otherwise an exception is thrown. The following is an example of a opening a table.

select * from openrowset('System.Data.OleDb', 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=northwind.mdb', shippers)

When query is specified, it much confirm to the syntax of the external database. The returned result-set is readonly. The following are some example queries:

select * from openrowset('System.Data.SqlClient', 'Data Source=.;Initial Catalog=pubs;Integrated Security=True', 'select * from titles')[1] AS 'January',[2] AS 'February',[3] AS 'March',[4] AS 'April',[5] AS 'May',[6] AS 'June',[7] AS 'July',[8] AS 'August',[9] AS 'September',[10] AS 'October',[11] AS 'November',[12] AS 'December'

FROM openrowset('myconnection',

'SELECT MONTH(OrderDate) AS ''MonthNumber'', COUNT(*) AS ''OrderCount'' FROM Sales.SalesOrderHeader WHERE Year(OrderDate) = 2002 GROUP BY MONTH(OrderDate)') AS Data

PIVOT(SUM(OrderCount) FOR MonthNumber IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PVT