A comprehensive SQL syntax reference covering commands, functions, operators, and concepts across MySQL, PostgreSQL, SQL Server, SQLite, and Oracle.
Don't want to memorize SQL syntax? Describe what you need in plain English and get the query instantly.
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
- Constraints
- Operators
- Functions
- Joins
- Subqueries
- Views
- Indexes
- Stored Procedures and Functions
- Common Clauses
- Data Types
- Window Functions
- Common Table Expressions (CTEs)
- Database-Specific Syntax
- Performance and Optimization
- Common Mistakes and Fixes
- Additional Resources
Create a Database
CREATE DATABASE database_name;Create a Table
CREATE TABLE table_name (
column1 datatype [constraints],
column2 datatype [constraints],
...
);Example:
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100) UNIQUE,
CreatedDate DATE DEFAULT CURRENT_DATE
);Add a Column
ALTER TABLE table_name
ADD column_name datatype [constraints];Modify a Column
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;Drop a Column
ALTER TABLE table_name
DROP COLUMN column_name;Drop a Table
DROP TABLE table_name;Drop a Database
DROP DATABASE database_name;Basic Syntax
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...];Example:
SELECT FirstName, LastName
FROM Customers
WHERE CreatedDate > '2023-01-01'
ORDER BY LastName ASC;Insert Into All Columns
INSERT INTO table_name
VALUES (value1, value2, ...);Insert Into Specific Columns
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);Example:
INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('Alice', 'Smith', 'alice@example.com');UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;Example:
UPDATE Customers
SET Email = 'newemail@example.com'
WHERE CustomerID = 1;DELETE FROM table_name
WHERE condition;Example:
DELETE FROM Customers
WHERE CustomerID = 1;GRANT privilege_type ON object_name TO user [WITH GRANT OPTION];Example:
GRANT SELECT, INSERT ON Customers TO 'db_user';REVOKE privilege_type ON object_name FROM user;Example:
REVOKE INSERT ON Customers FROM 'db_user';COMMIT;ROLLBACK;SAVEPOINT savepoint_name;Rollback to Savepoint
ROLLBACK TO SAVEPOINT savepoint_name;- NOT NULL: Ensures a column cannot have a NULL value.
- UNIQUE: Ensures all values in a column are unique.
- PRIMARY KEY: Uniquely identifies each record in a table.
- FOREIGN KEY: Ensures referential integrity between tables.
- CHECK: Ensures that all values in a column satisfy a specific condition.
- DEFAULT: Sets a default value for a column if none is specified.
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE NOT NULL,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);- Arithmetic Operators:
+,-,*,/,% - Comparison Operators:
=,<>or!=,>,<,>=,<= - Logical Operators:
AND,OR,NOT - Other Operators:
BETWEEN,IN,LIKE,IS NULL,EXISTS
Example:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 50;
SELECT * FROM Customers
WHERE Email LIKE '%@example.com';- COUNT(): Counts the number of rows.
- SUM(): Calculates the sum of a numeric column.
- AVG(): Calculates the average value.
- MIN(): Finds the minimum value.
- MAX(): Finds the maximum value.
Example:
SELECT COUNT(*) FROM Orders;
SELECT CustomerID, SUM(TotalAmount) as TotalSpent
FROM Orders
GROUP BY CustomerID;- UPPER(string): Converts to uppercase.
- LOWER(string): Converts to lowercase.
- SUBSTRING(string, start, length): Extracts a substring.
- TRIM(string): Removes whitespace.
- CONCAT(string1, string2, ...): Concatenates strings.
Example:
SELECT UPPER(FirstName), LOWER(LastName)
FROM Customers;- NOW(): Returns current date and time.
- CURDATE(): Returns current date.
- DATE_ADD(date, INTERVAL value unit): Adds a time interval to a date.
- DATEDIFF(date1, date2): Returns the difference between two dates.
Example:
SELECT NOW();
SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY);Returns records with matching values in both tables.
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;Example:
SELECT Orders.OrderID, Customers.FirstName, Customers.LastName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;Returns all records from the left table, and matched records from the right table.
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;Returns all records from the right table, and matched records from the left table.
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;Returns all records when there is a match in either left or right table.
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;Writing complex JOINs is error-prone. Describe your tables and relationships in English, and AI2SQL generates the correct JOIN for you.
A query nested inside another query.
SELECT column1
FROM table1
WHERE column2 = (SELECT column FROM table2 WHERE condition);Example:
SELECT FirstName, LastName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE TotalAmount > 1000);Nested subqueries getting complicated? Just describe what data you need. AI2SQL handles the nesting automatically.
A virtual table based on the result set of an SQL statement.
Create a View
CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE condition;Example:
CREATE VIEW HighValueOrders AS
SELECT OrderID, CustomerID, TotalAmount
FROM Orders
WHERE TotalAmount > 1000;Used to speed up the retrieval of data.
Create an Index
CREATE INDEX index_name
ON table_name (column1, column2, ...);Create a Unique Index
CREATE UNIQUE INDEX index_name
ON table_name (column);A set of SQL statements that can be stored and executed on the database server.
Create a Stored Procedure
CREATE PROCEDURE procedure_name (parameters)
BEGIN
-- SQL statements
END;Example:
CREATE PROCEDURE GetCustomerOrders(IN custID INT)
BEGIN
SELECT * FROM Orders WHERE CustomerID = custID;
END;A function that returns a single value.
Create a Function
CREATE FUNCTION function_name (parameters)
RETURNS datatype
BEGIN
DECLARE variable datatype;
-- SQL statements
RETURN variable;
END;Skip the boilerplate. Describe your procedure's logic in plain English and AI2SQL writes the full stored procedure.
Filters records that meet specific conditions.
SELECT * FROM table_name
WHERE condition;Groups rows that have the same values in specified columns.
SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1;Filters groups according to specified conditions.
SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1
HAVING condition;Sorts the result set.
SELECT * FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];Limits the number of records returned.
-
MySQL, PostgreSQL, SQLite
SELECT * FROM table_name LIMIT number OFFSET offset;
-
SQL Server
SELECT TOP number * FROM table_name;
-
Oracle
SELECT * FROM table_name FETCH FIRST number ROWS ONLY;
Tired of looking up syntax differences between databases? AI2SQL generates the right syntax for your specific database.
INTDECIMAL(p, s)FLOATDOUBLE
CHAR(n)VARCHAR(n)TEXT
DATETIMEDATETIMETIMESTAMP
BINARYVARBINARYBLOB
BOOLEANorBIT
-- ROW_NUMBER
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;
-- RANK and DENSE_RANK
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) as rank,
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;
-- Running total
SELECT date, amount,
SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;
-- LAG and LEAD
SELECT date, revenue,
LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
revenue - LAG(revenue, 1) OVER (ORDER BY date) as daily_change
FROM daily_sales;-- Basic CTE
WITH active_customers AS (
SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE LastOrderDate > DATE_SUB(NOW(), INTERVAL 90 DAY)
)
SELECT * FROM active_customers;
-- Recursive CTE (org chart)
WITH RECURSIVE org_chart AS (
SELECT id, name, manager_id, 1 as level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;-- UPSERT
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
-- JSON queries
SELECT data->>'name' as name FROM events WHERE data->>'type' = 'signup';
-- Array operations
SELECT * FROM posts WHERE tags @> ARRAY['sql', 'tutorial'];-- UPSERT
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);
-- JSON queries
SELECT JSON_EXTRACT(data, '$.name') FROM events;
-- Full-text search
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('sql tutorial');-- MERGE (UPSERT)
MERGE INTO users AS target
USING (VALUES ('a@b.com', 'Alice')) AS source (email, name)
ON target.email = source.email
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT (email, name) VALUES (source.email, source.name);
-- STRING_AGG
SELECT department, STRING_AGG(name, ', ') as team
FROM employees GROUP BY department;-- EXPLAIN query plan
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- Index hints (MySQL)
SELECT * FROM orders USE INDEX (idx_customer) WHERE customer_id = 42;
-- Avoid SELECT *
SELECT id, name, email FROM users WHERE active = 1; -- faster than SELECT *
-- Use EXISTS instead of IN for large datasets
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);-- WRONG: NULL comparison
SELECT * FROM users WHERE status = NULL;
-- CORRECT:
SELECT * FROM users WHERE status IS NULL;
-- WRONG: GROUP BY with non-aggregated columns
SELECT name, department, COUNT(*) FROM employees GROUP BY department;
-- CORRECT:
SELECT department, COUNT(*) FROM employees GROUP BY department;
-- WRONG: Using HAVING instead of WHERE
SELECT * FROM orders HAVING total > 100;
-- CORRECT:
SELECT * FROM orders WHERE total > 100;
-- WRONG: Cartesian join (missing JOIN condition)
SELECT * FROM orders, customers;
-- CORRECT:
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;- AI2SQL - Generate SQL queries from plain English descriptions. Supports MySQL, PostgreSQL, SQL Server, SQLite, and Oracle. No SQL knowledge required.
- Learning: Use this guide as a starting point to learn SQL syntax and concepts.
- Reference: Quickly look up syntax for SQL commands and functions.
- Contribution: Feel free to contribute by suggesting improvements or adding new sections.
This cheat sheet is released under the MIT License.
Note: SQL syntax may vary slightly between different database systems (e.g., MySQL, PostgreSQL, SQL Server, Oracle). Always refer to the official documentation of the database you are using for exact syntax and additional features.
Writing SQL by hand? Describe what you need in English and get the query instantly.