SQL Syntax

Query Syntax of SQL Query Language

This page describes the syntax of SQL. Each select statement in SQL follows precise syntactical and structural rules. The following statement is the minimum structure and syntax required for an SQL SELECT statement.

 SELECT [DISTINCT | ALL] {* | select_list} 
 FROM {table_name [alias] | view_name}

An SQL statement is a valid combination of tokens introduced by a keyword. Tokens include keywords, identifiers, operators, literals, and punctuation symbols.



Table of contents

    SQL Keywords
    Identifiers
    Naming conventions
    SQL Literals
    Operators
    Operator precedence
    SQL Comments and Whitespaces
    SQL language elements
    List of SQL Keywords
 

SQL Keywords

Keywords (e.g. SELECT, GRANT, DELETE, or CREATE ) are words that SQL reserves and they have a predefined meaning in the language. Using a keyword outside its specific context causes an error. In practice you can use keywords in upper or lower case letters. Following three statements are equal.

SELECT * FROM EMPLOYEES;  

  1.       

Select * FROM EMPLOYEES;  

  1.       

select * FROM EMPLOYEES;  

  1.       

In some cases keywords can be abbreviated. For example DESCRIBE can be used as either DESC or DESCRIBE. If we execute following commands, in both cases it will show the structure of the employees tables.

 

  1. DESCRIBE EMPLOYEES;  
  2.       

DESC EMPLOYEES;  

 

Identifiers

Identifiers are the names given by database designer or system users to database objects such as tables, columns, aliases, indexes, views and other objects as well as the database itself. In the last example 'EMPLOYEES' is an identifier and 'SELECT' is the keyword. Keywords and identifiers have the same lexical structure, i.e. one cannot know whether a token is an identifier or a keyword without knowing the language. The rules to create an identifier is vendor specification. See the following table.

 

Rules Platform Description
Identifiers should contain between SQL2003 128 characters
  DB2 128 characters, depending on the object
  MySQL 64 characters
  Oracle 30 bytes; database names are limited to 8 bytes
  PostgreSQL 31 characters
Identifier may contain SQL2003 Any number, character, or underscore
  DB2 Any number, uppercase character, digit, or the underscore character
  MySQL Any number, character, or symbol
  Oracle Any number, character, and the underscore (_), pound (#), and dollar ($) symbols
  PostgreSQL Any number, character, or the underscore (_)symbol
The first character must be SQL2003 A letter
  DB2 A letter
  MySQL A letter or number (but should not be all numbers
  Oracle A letter
  PostgreSQL A letter or underscore (_)
Identifier cannot contain SQL2003 Special characters or spaces
  DB2 Special characters or spaces
  MySQL Period (.), slash (/), or ASCII(0) and ASCII(255). Quote (') and double-quote (") are only allowed in quoted identifiers.
  Oracle Spaces, double-quotes ("), or special characters
  PostgreSQL Double-quote (")
Quoted identifier symbol SQL2003 Double-quote (")
  DB2 Double-quote (")
  MySQL Quote ( ' ) or double-quote (" ) in ANSI compatibility mode
  Oracle Double-quote (")
  PostgreSQL Double-quote (")
Identifier may be reserved SQL2003 No, unless as a quoted identifier
  DB2 Yes
  MySQL No, unless as a quoted identifier
  Oracle No, unless as a quoted identifier
  PostgreSQL No, unless as a quoted identifier
Schema addressing SQL2003 Catalog.schema.object
  DB2 Schema.object
  MySQL Database.object
  Oracle Schema.object
  PostgreSQL Database.schema.object
Identifier must be unique SQL2003 Yes
  DB2 Yes
  MySQL Yes
  Oracle Yes
  PostgreSQL Yes

 

Naming conventions

There are various naming conventions that are all valid when creating tables, attributes, queries and other objects in a SQL database. The SQL standard has no comment on naming conventions, you can follow these basic guidelines :

- Select a name that is meaningful, significant, and descriptive. For example a table name should be employee not emp, column name of first name in employee table should be first_name not fname though both 'emp' and 'fname' are valid identifiers.

- Maintain same case throughout. Use either all uppercase or all lowercase for all objects in a SQL database as some database servers are case-sensitive.

SQL Literals

The terms literal refer to a fixed data value. SQL evaluates four type of literal values numeric, character string, date or time, or Boolean value though SQL database offers variety of literal values in a SQL program. For example 100, -120, 544.03, -458.25, 3E2, 5E-2 are valid numeric literals. 'USA', '2000', 'SQL Syntax', 'Jan 01, 1981' are valid character string (should enclosed by by single quotation marks (' ')). Boolean and date literals look like TRUE and 'JAN-28-1976 21:12:40:00'

 

Operators

An operator manipulates individual data items and returns a result. Operators are used in various SQL operations like SELECT, INSERT, UPDATE or DELETE or in various database objects creation like functions, views, triggers and stored procedures. SQL supports various types of operators though all databases do not supports all operators. See the following tables :

Operators

Works in

Arithmetic operators

All databases

Assignment operators

All databases

Bitwise operators

Microsoft SQL Server

Comparison operators

All databases

Logical operators

DB2, Oracle, SQL Server, and PostgreSQL

Unary operators

DB2, Oracle, and SQL Server

In the next session we have discussed all the operators in detail with examples.

 

Operator precedence

Precedence is the order in which database evaluates different operators in the same expression. When evaluating an expression containing multiple operators (e.g. +, -, /), operator precedence evaluates operators with higher precedence before evaluating those with lower precedence. Operator precedence evaluates operators with equal precedence from left to right within an expression. If there are parentheses within the expression then it evaluated first and the rest part which are outside the parentheses are evaluated next. The following table lists the levels of precedence among SQL operators from high to low.

Precedence order

( ) (parenthetical expressions)

+, -, ~ (unary operators)

*, /, % (mathematical operators)

+, - (arithmetic operators)

=, >, <, >=, <=, <>, !=, !>, !< (comparison operators)

I^ (Bitwise Exclusive OR), & (Bitwise AND), | (Bitwise OR)

NOT

AND

ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

= (variable assignment)

The following expression in MySQL query return different results :

 

  1. SELECT 12 * 2 + 24;   

Output

12 * 2 + 24

48

Operator precedence

sql operator precedence expression2

 

  1. SELECT 12 * (2 + 24)  

Output


 

 

12 * (2 + 24)

312

Operator precedence

sql operator precedence -expression2

 

SQL Comments Whitespaces

A comment is optional text that describe what a program does and how, or why code was modified. Compiler always ignores comments. A comment is introduced by double dashes followed by a space e.g.:
-- This is SQL comment

Alternatively, C-style block comments can be used :

/* This is the first line comment
This is the second line comment */ .

Whitespaces

Whitespaces is generally ignored in SQL statements, which makes easier to format SQL code for readability.

 

The following chart shows some SQL language elements that compose a single statement :

sql language elements

 

List of SQL Keyword :

ABSOLUTE

  ACTION ADD ADMIN
AFTER AGGREGATE ALIAS ALL
ALLOCATE ALTER AND ANY
ARE ARRAY AS ASC
ASSERTION ASSERTION AT ATOMIC
AUTHORIZATION BEFORE BEGIN BIGINT
BINARY BIT BLOB BOOLEAN
BOTH BREADTH BY CALL
CASCADE CASCADED CASE CAST
CATALOG CHAR CHARACTER CHECK
CLASS CLOB CLOSE COLLATE
COLLATION COLLECT COLUMN COMMIT
COMPLETION CONDITION CONNECT CONNECTION
CONSTRAINT CONSTRAINTS CONSTRUCTOR CONTAINS
CONTINUE CORRESPONDING CREATE CROSS
CUBE CURRENT CURRENT_DATE CURRENT_PATH
CURRENT_ROLE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER
CURSOR CYCLE DATA DATALINK
DATE DAY DEALLOCATE DEC
DECIMAL DECLARE DEFAULT DEFERRABLE
DELETE DEPTH DEREF DESC
DESCRIPTOR DESTRUCTOR DIAGNOSTICS DICTIONARY
DISCONNECT DO DOMAIN DOUBLE
DROP ELEMENT END-EXEC EQUALS
ESCAPE EXCEPT EXCEPTION EXECUTE
EXIT EXPAND EXPANDING FALSE
FIRST FLOAT FOR FOREIGN
FREE FROM FUNCTION FUSION
GENERAL GET GLOBAL GOTO
GROUP GROUPING HANDLER HASH
HOUR IDENTITY IF IGNORE
IMMEDIATE IN INDICATOR INITIALIZE
INITIALLY INNER INOUT INPUT
INSERT INT INTEGER INTERSECT
INTERSECTION INTERVAL INTO IS
ISOLATION ITERATE JOIN KEY
LANGUAGE LARGE LAST LATERAL
LEADING LEAVE LEFT LESS
LEVEL LIKE LIMIT LOCAL
LOCALTIME LOCALTIMESTAMP LOCATOR LOOP
MATCH MEMBER MEETS MERGE
MINUTE MODIFIES MODIFY MODULE
MONTH MULTISET NAMES NATIONAL
NATURAL NCHAR NCLOB NEW
NEXT NO NONE NORMALIZE
NOT NULL NUMERIC OBJECT
OF OFF OLD ON
ONLY OPEN OPERATION OPTION
OR ORDER ORDINALITY OUT
OUTER OUTPUT PAD PARAMETER
PARAMETERS PARTIAL PATH PERIOD
POSTFIX PRECEDES PRECISION PREFIX
PREORDER PREPARE PRESERVE PRIMARY
PRIOR PRIVILEGES PROCEDURE PUBLIC
READ READS REAL RECURSIVE
REDO REF REFERENCES REFERENCING
RELATIVE REPEAT RESIGNAL RESTRICT
RESULT RETURN RETURNS REVOKE
RIGHT ROLE ROLLBACK ROLLUP
ROUTINE ROW ROWS SAVEPOINT
SCHEMA SCROLL SEARCH SECOND
SECTION SELECT SEQUENCE SESSION
SESSION_USER SET SETS SIGNAL
SIZE SMALLINT SPECIFIC SPECIFICTYPE
SQL SQLEXCEPTION SQLSTATE SQLWARNING
START STATE STATIC STRUCTURE
SUBMULTISET SUCCEEDS SUM SYSTEM_USER
TABLE TABLESAMPLE TEMPORARY TERMINATE
THAN THEN TIME TIMESTAMP
TIMEZONE_HOUR TIMEZONE_MINUTE TO TRAILING
TRANSACTION TRANSLATION TREAT TRIGGER
TRUE UESCAPE UNDER UNDO
UNION UNIQUE UNKNOWN UNTIL
UPDATE USAGE USER USING
VALUE VALUES VARCHAR VARIABLE
VARYING VIEW WHEN WHENEVER
WHERE WHILE WITH WRITE
YEAR ZONE