SQL Reference

SELECT Statement Syntax

SELECT select_items

FROM from-clause

[WHERE criteria]

[GROUP BY grouping_items
[HAVING having_clause]]

ORDER BY sort_items

Click the More Info button in each section below, to see SQL examples demonstrating the supported syntax.

SELECT select_items

Select_items can be any of the following. Multiple select_items are comma separated.

• Distinct
• Result set limiters
• Column name(s)
• Column name(s) with an alias.
• Table-qualified column name(s)
• Table-alias qualified column name(s)
• Aggregate Functions
• SQL92-Compliant Functions
• Omnidex Extended Functions
• Literals
• Expressions
• $ROWID, $ODXID

top

FROM Clause

The FROM clause can contain any of the following:

• Single Table
• Multiple Tables (Joins)
• Join columns must have matching data types and lengths
• Inner Joins (Standard SQL Syntax) - Join occurs in the WHERE clause
• Outer Joins (Standard SQL Syntax) - Left Outer Join or Right Outer Join
• Nested Select

top

WHERE Clause

WHERE clause criteria can contain any of the following:

• left_operand = right_operand (equal to)
• left_operand < right_operand (less than)
• left_operand > right_operand (greater than)
• left_operand <= right_operand (less than or equal to)
• left_operand >= right_operand (greater than or equal to)
• left_operand <> right_operand (not equal to)
• left_operand != right_operand (not equal to)
• left_operand in (value-list)
• left_operand like right_operand_with_wildcard
• exists (Nested Select) - MUST RETURN ONLY ONE COLUMN.
• =, <, >, <=, >=, <>, like - Nested Select that GUARANTEES to return ONLY ONE ROW. MUST RETURN ONLY ONE COLUMN.
• in (Nested Select) - Can return 0 or more rows. MUST RETURN ONLY ONE COLUMN.
• NOT, AND, OR with any of the above

left_operand and right_operand can contain any of the following:

• Columns
• Table Qualified Columns - table.column
• Column alias
• Literals
• Constants
• Expressions
• $VALUES, $ROWID, $ODXID
• IS NULL - This will not be an optimized query!

Use parenthesis to force order of precedence.

top

GROUP BY Clause

A GROUP BY clause can contain any of the following:

• Column names from select list
• Column names not in select list
• HAVING Clause

HAVING Clause

A HAVING Clause can contain any of the following:

• Column Value Criteria
• Column Value Aggregations
• Nested Selects

top

ORDER BY Clause

An ORDER BY clause can contain any of the following:

• Column Name(s) - Comma separated
• Column Alias - Comma separated - Must be in select item list
• Select List Item Ordinal Numbers - Comma separate - In order of select item list starting from 1
• Aggregates, Functions, Expressions - Must be in select item list - Must be referred to ordinally (select list item number) - Comma separated
• ASC (default) or DESC - With any of the above. DESC sorts will not be optimized.

top