w3resource logo


>SQL SYNTAX

SQL Syntax

Secondary Nav

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.

Contents:

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;
    
Select * FROM EMPLOYEES;
    
select * FROM EMPLOYEES;
    

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.

DESCRIBE EMPLOYEES;
    
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 a 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 support 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 :

SELECT 12 * 2 + 24; 

Output

12 * 2 + 24
48

Operator precedence

sql operator precedence expression2

SELECT 12 * (2 + 24)

Output

12 * (2 + 24)
312

Operator precedence

sql operator precedence -expression2

SQL Comments Whitespaces

A comment is an optional text that describes what a program does and how, or why the code was modified. The 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 are 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


Join our Question Answer community to learn and share your programming knowledge.

Help the community:

Python: Fizzbuzz

C++: Decimal to binary conversion

JavaScript: Need Help in JavaScript

Python: Help me with this program