SQL Syntax

Here is the syntax for the SQL SELECT statement that the SQL parser recognizes.

Considerations

• SQL syntax is case insensitive.
• Field and table names are case sensitive

Legend

• [ x ] - Optional (one or none)
• [, x ... ] - Optional additional values, in a comma-separated list
• x | y - Choose one. Curly braces are added when needed: { x | y } ...
• CAPITALIZED - SQL Keyword (case insensitive)
• italicized - Value you supply. (Table names and column names are case-sensitive)

SELECT Statement

In a select statement, you designate one more columns separated by commas (or "*" for all columns), plus a table or join to get the data from, and additional options:

Examples

SELECT * FROM Customers
SELECT * FROM Customers LIMIT 80
SELECT * FROM Customers ORDER BY zip_code, customer_name

Note: The "Order" table must be enclosed in backticks, because that name is a reserved word in SQL.

Syntax

SELECT

[DISTINCT]

[(] column_expr [, column_expr ...] [)]

FROM tables

[WHERE where_clause]

[GROUP BY col_name [ASC|DESC]  [, col_name [ASC|DESC] ...]

[WITH ROLLUP]

[HAVING where_clause]

]

[ORDER BY col_name [ASC|DESC]  [, col_name [ASC|DESC] ...]

[LIMIT {maximum_rows | offset, maximum_rows} ]

where:

DISTINCT

Eliminates duplicate rows from the result set.

For example: DISTINCT(customer_name, address)

(Parentheses are optional, but must be matched if present.)

WITH ROLLUP

Adds summary rows that aggregate values for each group.

HAVING

Is only allowed as part of GROUP BY, for performance reasons. (WHERE does the same thing outside of a GROUP BY clause, only more efficiently.)

ASC

Ascending (the default).

DESC

Descending.

maximum_rows

Maximum number of rows to return.

offset

The row to start from. Offset for the first row is zero (0).

Learn more:

• MySQL Select Statement syntax

column_expr

*  |  table_alias.*  |  col_name [ [AS] col_alias ]  |  expr [AS] col_alias

where:

col_alias

Used for the column name in the result set in the SQL Browser, or for the tag name in the REST execSQL resource.

Required when an expression is specified.

Can be used as a field name in a group_by_clause or order_by_clause.

For example:

SELECT CONCAT(last_name,', ',first_name) AS full_name

FROM Customer_Contacts ORDER BY full_name;

tables

Example

SELECT cust.customer_name, orders.order_amount FROM `Order` as orders, Customer AS cust GROUP BY cust.customer_name WITH ROLLUP
...

Syntax

[(] table [, table ...] [)]

table

  table_name [ [AS] alias ]

| join_table

join_table

table [INNER | LEFT | RIGHT] JOIN tables

[ON on_clause]

on_clause

on_condition [ {AND|OR} on_condition ...]

on_condition

  table_alias.col_name IS [NOT] NULL

| table_alias.col_name = table_alias.col_name

| table_alias.col_name != table_alias.col_name

| ( on_clause )

where:

• If multiple tables are specified, a JOIN is implied
• An INNER join (aka a STRAIGHT join) is the default.
• table_alias is required when specifying columns in a JOIN.

where_clause

An expression that evaluates to true or false for a row. When true, the row is selected.

Example

SELECT orders.order_number, Item.amount FROM `Order` as orders, OrderItems AS Item WHERE Item.related_to_Orders = orders.id ORDER BY orders.order_number

Syntax

condition [ {AND|OR} condition ...]

condition

  expr IS [NOT] NULL

| expr [NOT] LIKE wildcard_string

| expr [NOT] REGEXP pattern_string

| expr [NOT] IN (expr [, expr ...])

| expr = expr

| expr < expr

| expr > expr

| expr != expr

| expr <= expr

| expr >= expr

expr

  string

| number

| date/time

| column_name

| SQL Function

| + expr

| - expr

| (expr [, expr ...] )

wildcard_string

A string containing one or more wildcards, used in a [NOT] LIKE expression:

xx	yy
%	Matches any number of characters, or zero characters.
_	Matches exactly one character.

pattern_string

A string containing a regular expression, used in a [NOT] REGEXP expression:

Character(s)	Meaning
.	Any character, including newlines.
*	Zero or more of previous character. As in: '.*'
+	One or more of previous character. As in: 't+'
?	Zero or one of previous character. As in: 'T?'
|	XYZ'
(...)	XYZ) Company', '(ABC|XYZ)( Company)?' (The latter pattern matches "ABC", "XYC", "ABC Company", and "XYZ Company".)
[...]	Character set: [abcdef] - match any of the characters in the set [a-f] - match any of the characters in the range [a-fA-FzZ] - Combination, Match any of the specified characters,or any of the characters in either range. [^...] - Negation. Match anything other than the characters specified in the set.
^	Beginning of a string when it is in a pattern. "Not", when it is the first character in a character set.
$	End of string.

Note:
Since strings use backslash escape syntax (for example, “\n” to represent the newline character), you must double any backslash characters you use in your REGEXP strings. For example, in a regular expression, a question mark (?) means "zero or one". To match a question mark in a string, the pattern matcher needs to see \?. (So it looks for the question mark, instead of interpreting it as "zero or one".) To get that string to the pattern matcher, you specify \\?. That way, string processing reduces the \\ to \, and regular expression processor sees \?, as intended.

For example, this expression evaluates as true:

SELECT 'Question?' REGEXP 'Q.*\\?'}}

string

A sequence of alphanumeric characters and/or symbols, enclosed in single quotes:

'a string'

Strings can also "escape sequences" for characters that do not appear on the keyboard:

Escape Sequence	Character
\0	ASCII NUL (0x00).
\'	Single quote (“'”).
\"	Double quote (“"”).
\b	Backspace character.
\n	Newline (linefeed).
\r	Carriage return.
\t	Tab character.
\\	Backslash (“\”).
\%	The “%” character. Used in a [NOT] LIKE expression, so the character isn't interpreted as a wildcard.
\_	The “_” character. Used in a [NOT] LIKE expression, so the character isn't interpreted as a wildcard.
\Z	ASCII 26 (Control+Z). To prevent the character from being interpreted as End-of-File in Windows.

number

...

date/time

...