SQL Queries

Structured Query Language (SQL) is a standard language for accessing and manipulating databases. You use SQL when you work with relational queries in CDM.

You can use SQL statements to perform the following types of database-related tasks:

Execute queries against the database
Retrieve data from the database

SQL statements have a specific syntax and require certain information, which can be difficult to understand. When you select what you want to include in a SQL statement in CDM, you can ensure that your statement is structured correctly.

Important: The advanced functionality for Microsoft™ products is beyond the scope of this guide. However, some topics are included for your convenience. For more information, see Microsoft documentation or Microsoft online help.

Keywords, Clauses, and Statements

You can use SQL statements in relational queries in CDM to perform most of the activities that you need to perform in a database.

When you query relational data sources in CDM, you must retrieve data through SELECT statements. If you use SQL statements other than SELECT, such as UPDATE or DELETE, they are reported as errors. Use of a semicolon is not supported.

Note: SQL itself is not case-sensitive. Names can be case-sensitive, and they can contain spaces and other delimiters if they are surrounded by double quotation marks.

The following statement shows the structure of a basic SQL statement:

SELECT VendorName FROM Table_Vendor

Keyword	Description
SELECT	The `SELECT` clause is mandatory, and specifies a list of columns to be retrieved. The result is stored in a result table, called the result set. `SELECT column_names` Tip: The asterisk () is a quick way of selecting all columns in a table. For example: `SELECT FROM table_name`
FROM	The `FROM` clause is mandatory and always follows the `SELECT` clause, and lists the tables to be accessed by the query. `SELECT column_names FROM table_name`
WHERE	Optional. When used, the `WHERE` clause always follows the `FROM` clause. `WHERE` filters rows in the tables specified in the `FROM` clause. If the `WHERE` clause is not included, all rows are used. `SELECT column_names FROM table_name WHERE conditional_expression`

Keyword

Description

SELECT

The SELECT clause is mandatory, and specifies a list of columns to be retrieved. The result is stored in a result table, called the result set.

SELECT column_names

Tip: The asterisk (*) is a quick way of selecting all columns in a table. For example:

SELECT * FROM table_name

FROM

The FROM clause is mandatory and always follows the SELECT clause, and lists the tables to be accessed by the query.

SELECT column_names FROM table_name

WHERE

Optional. When used, the WHERE clause always follows the FROM clause. WHERE filters rows in the tables specified in the FROM clause. If the WHERE clause is not included, all rows are used.

SELECT column_names FROM table_name WHERE conditional_expression

Comparison Operators

The WHERE clause can use comparison operators as part of the expression in a relational query in CDM. The result of a WHERE expression is always evaluated to true, false, or unknown.

Operator	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to

Logical Operators

The WHERE clause in a SQL statement can use logical operators as part of an expression in a relational query in CDM. Like comparison operators, logical operators evaluate to true, false, or unknown.

Logical Operator	Description
AND	This operator combines two logical conditions. AND requires that each must be met for the record to be included in the result set. When you use the AND and OR operators together, AND has a greater precedence than OR. `SELECT columns FROM tables WHERE column1 = value1 AND column2 = value2` In this case, `column1` must equal `value1` and `column2` must equal `value2`, or no record is returned in the result set.
OR	This operator combines two logical conditions. OR requires that any of the conditions must be met for the record to be included in the result set. When you use the AND and OR operators together in an expression, OR has a lesser precedence than AND. `SELECT columns FROM tables WHERE column1 = value1 OR column2 = value2` In this case, either `column1` or `column2` must equal the stated value, or no record is returned in the result set.
NOT	This operator inverts the result of a comparison expression or a logical expression. To be able to return a record in the result set, NOT requires that none of the conditions are met. `SELECT columns FROM tables WHERE NOT column1 = value1` In this case, `column1` cannot equal `value1`, or no record is returned in the result set.

Logical Operator

Description

AND

This operator combines two logical conditions. AND requires that each must be met for the record to be included in the result set. When you use the AND and OR operators together, AND has a greater precedence than OR.

SELECT columns FROM tables WHERE column1 = value1 AND column2 = value2

In this case, column1 must equal value1 and column2 must equal value2, or no record is returned in the result set.

This operator combines two logical conditions. OR requires that any of the conditions must be met for the record to be included in the result set. When you use the AND and OR operators together in an expression, OR has a lesser precedence than AND.

SELECT columns FROM tables WHERE column1 = value1 OR column2 = value2

In this case, either column1 or column2 must equal the stated value, or no record is returned in the result set.

NOT

This operator inverts the result of a comparison expression or a logical expression. To be able to return a record in the result set, NOT requires that none of the conditions are met.

SELECT columns FROM tables WHERE NOT column1 = value1

In this case, column1 cannot equal value1, or no record is returned in the result set.

Joins and Unions

You can use a SQL statement to create joins and unions between tables in a relational query in CDM.

Query Type	Description
JOIN	You can use a join statement to combine rows from multiple tables. An inner join is the most common, and returns all rows from multiple tables where the join condition is met. An outer join is used to include rows that exist in one table (but not the other). A left outer join preserves rows from the table specified before the join statement. A right outer join preserves rows from the table specified after the join statement. For example, an inner join might look like this: `SELECT * FROM tableA JOIN tableB on column_nameA = column_nameB`
UNION	You can use a union statement to combine the result sets of two or more `SELECT` queries. By default, it removes duplicate rows between the various `SELECT` statements. Using the `ALL` qualifier forces duplicate rows to persist in the query result. For example, a union of 2 tables with duplicate rows preserved might look like this: `SELECT column_nameA FROM tableA UNION ALL SELECT column_nameB FROM tableB`

Query Type

Description

JOIN

You can use a join statement to combine rows from multiple tables. An inner join is the most common, and returns all rows from multiple tables where the join condition is met. An outer join is used to include rows that exist in one table (but not the other). A left outer join preserves rows from the table specified before the join statement. A right outer join preserves rows from the table specified after the join statement. For example, an inner join might look like this:

SELECT * FROM tableA JOIN tableB on column_nameA = column_nameB

UNION

You can use a union statement to combine the result sets of two or more SELECT queries. By default, it removes duplicate rows between the various SELECT statements. Using the ALL qualifier forces duplicate rows to persist in the query result. For example, a union of 2 tables with duplicate rows preserved might look like this:

SELECT column_nameA FROM tableA UNION ALL SELECT column_nameB FROM tableB

SQL Queries

Keywords, Clauses, and Statements

Comparison Operators

Logical Operators

Joins and Unions

Was this article helpful?