SQL reference
SQL Guides
Each guide explains one SQL statement or clause with its syntax, worked examples, the places where PostgreSQL, MySQL, SQL Server and SQLite differ, and the mistakes people commonly make.
Querying
Read rows, shape columns and combine result sets.
- SQL AS (alias)Use SQL AS to give columns and tables temporary names. Learn column aliases, table aliases, quoting rules and where an alias can and cannot be used.
- SQL CASEUse the SQL CASE expression to add if-then-else logic to queries. See simple and searched CASE, CASE in ORDER BY and aggregates, and dialect notes.
- SQL FROMThe SQL FROM clause names the tables, views or subqueries a query reads from. Learn aliases, joins and derived tables with clear, practical examples.
- SQL ORDER BYSort query results with SQL ORDER BY. Learn ascending and descending order, sorting by several columns, NULL ordering and dialect differences with examples.
- SQL SELECTLearn how the SQL SELECT statement reads data from tables: choosing columns, using expressions, filtering and sorting, with examples for every major database.
- SQL SELECT DISTINCTSQL SELECT DISTINCT returns only unique values or unique rows. See how it works with one or many columns, with COUNT, and how each database handles it.
- SQL SELECT INTOSELECT INTO creates a new table from a query result. Learn the SQL Server syntax and the CREATE TABLE AS equivalents for MySQL, PostgreSQL, SQLite and Oracle.
- SQL SELECT TOP / LIMITLimit the number of rows a query returns. Compare SQL Server TOP, MySQL and PostgreSQL LIMIT, FETCH FIRST and Oracle ROWNUM with side-by-side examples.
- SQL UNIONSQL UNION stacks the results of two or more SELECT statements and removes duplicate rows. Learn the rules, syntax, ordering and differences across databases.
- SQL UNION ALLSQL UNION ALL appends the rows of several SELECT statements and keeps every duplicate. See syntax, performance reasons to prefer it and dialect differences.
Filtering
Keep only the rows that match a condition.
- SQL ALLThe SQL ALL operator is true when a comparison holds for every value returned by a subquery. See syntax, examples, NULL traps and dialect notes.
- SQL ANDThe SQL AND operator returns rows only when every condition is true. See syntax, precedence over OR, examples and common mistakes.
- SQL ANYThe SQL ANY (and SOME) operator is true if a comparison holds for at least one value returned by a subquery. See syntax, examples and dialect notes.
- SQL BETWEENThe SQL BETWEEN operator selects values inside an inclusive range of numbers, dates or text. Includes NOT BETWEEN, date pitfalls and examples.
- SQL EXISTSThe SQL EXISTS operator returns true when a subquery finds at least one row. Compare with IN and JOIN, use NOT EXISTS, with examples.
- SQL INThe SQL IN operator tests a value against a list or subquery. Learn syntax, NOT IN, NULL behavior, examples and dialect differences.
- SQL IS NULLTest for missing values with SQL IS NULL and IS NOT NULL. Learn why = NULL fails, three-valued logic, COALESCE and dialect differences.
- SQL LIKEUse the SQL LIKE operator with % and _ wildcards to search text. Covers NOT LIKE, ESCAPE, case sensitivity, performance and dialect differences.
- SQL NOTThe SQL NOT operator reverses a condition. See NOT IN, NOT LIKE, NOT BETWEEN and NOT EXISTS with examples, NULL pitfalls and dialect notes.
- SQL ORThe SQL OR operator returns a row when at least one condition is true. Learn the syntax, precedence with AND, examples and pitfalls.
- SQL WHERELearn how the SQL WHERE clause filters rows in SELECT, UPDATE and DELETE statements, with comparison operators, examples and dialect notes.
Joins
Combine rows from two or more tables.
- SQL FULL OUTER JOINSQL FULL OUTER JOIN returns all rows from both tables, matching where possible. See syntax, examples, the MySQL UNION workaround and SQLite 3.39 support.
- SQL INNER JOINLearn how SQL INNER JOIN returns only rows with matches in both tables, with syntax, multi-table examples and dialect notes for major databases.
- SQL LEFT JOINSQL LEFT JOIN returns every row from the left table plus matching rows from the right. See syntax, anti-join patterns, WHERE vs ON pitfalls and dialect notes.
- SQL RIGHT JOINSQL RIGHT JOIN returns all rows from the right table and matching rows from the left. Learn the syntax, how it relates to LEFT JOIN, and dialect notes.
Aggregation
Summarise rows into groups and totals.
- SQL GROUP BYSQL GROUP BY collapses rows that share values into groups so you can use COUNT, SUM and AVG. See syntax, multi-column grouping, pitfalls and dialect notes.
- SQL HAVINGSQL HAVING filters grouped results using aggregate functions such as COUNT and SUM. Learn how it differs from WHERE, with syntax, examples and dialect notes.
Tables, views & databases (DDL)
Create, change and remove database objects.
- SQL ALTER TABLEAdd, rename, change and drop columns on an existing table, with PostgreSQL, MySQL and SQLite examples.
- SQL BACKUP DATABASELearn SQL Server BACKUP DATABASE for full, differential and log backups, plus how PostgreSQL, MySQL, SQLite and Oracle handle backups without that statement.
- SQL CREATE DATABASEHow to use SQL CREATE DATABASE to make a new database, set character set, collation and owner, and see how PostgreSQL, MySQL and SQL Server differ.
- SQL CREATE INDEXLearn how SQL CREATE INDEX works, when an index helps, how to index multiple columns, and the syntax differences between PostgreSQL, MySQL and SQL Server.
- SQL CREATE PROCEDURELearn SQL CREATE PROCEDURE to save reusable logic, pass parameters and run it with EXEC or CALL. Syntax and examples for SQL Server, MySQL and PostgreSQL.
- SQL CREATE TABLELearn SQL CREATE TABLE with column data types, constraints, defaults, copying a table from a query, and dialect differences.
- SQL CREATE UNIQUE INDEXUse SQL CREATE UNIQUE INDEX to block duplicate values in one or more columns. Syntax, NULL handling and partial unique indexes across major databases.
- SQL CREATE VIEWLearn how to create and replace a SQL view: syntax for CREATE VIEW and CREATE OR REPLACE VIEW, examples with joins, and dialect differences.
- SQL DROP COLUMNHow to use ALTER TABLE ... DROP COLUMN to remove a column, handle constraints and indexes that block it, and syntax differences across major databases.
- SQL DROP DATABASEUnderstand SQL DROP DATABASE, which permanently deletes a database and all its objects, with safe-use tips and dialect differences.
- SQL DROP INDEXHow to use SQL DROP INDEX to remove an index in PostgreSQL, MySQL, SQL Server, SQLite and Oracle, with syntax differences, examples and common pitfalls.
- SQL DROP TABLELearn SQL DROP TABLE to remove a table and its data, use IF EXISTS and CASCADE, handle foreign key errors, and compare with TRUNCATE and DELETE.
- SQL DROP VIEWLearn the SQL DROP VIEW syntax to remove a view in PostgreSQL, MySQL, SQL Server, SQLite and Oracle, including IF EXISTS, CASCADE and what happens to data.
- SQL TRUNCATE TABLELearn SQL TRUNCATE TABLE to remove every row fast while keeping the table, how it differs from DELETE and DROP, and behavior across major databases.
Constraints
Rules that keep table data valid.
- SQL ADD CONSTRAINTUse ALTER TABLE ADD CONSTRAINT to add primary key, foreign key, unique, check and default constraints to an existing table, with examples for each.
- SQL CHECKThe SQL CHECK constraint restricts the values a column can hold with a boolean rule. See column and table-level syntax, examples and dialect support.
- SQL DEFAULTThe SQL DEFAULT constraint supplies a value when an insert leaves a column out. See syntax, examples with CURRENT_TIMESTAMP, and dialect differences.
- SQL DROP CONSTRAINTLearn how to drop primary key, foreign key, unique and check constraints with ALTER TABLE, including the different MySQL and SQL Server syntax.
- SQL DROP DEFAULTRemove a column default with ALTER TABLE DROP DEFAULT. Shows PostgreSQL, MySQL, SQL Server and Oracle syntax, including dropping the named constraint.
- SQL FOREIGN KEYUnderstand the SQL FOREIGN KEY constraint, how it links tables, and how ON DELETE CASCADE works. Includes examples for creating and altering keys.
- SQL NOT NULLUse the SQL NOT NULL constraint to force a column to always hold a value. Includes create and alter examples and dialect syntax for MySQL and others.
- SQL PRIMARY KEYLearn how the SQL PRIMARY KEY constraint uniquely identifies each row. Covers composite keys, auto-increment columns, and adding or dropping keys.
- SQL UNIQUEThe SQL UNIQUE constraint stops duplicate values in a column or column group. See syntax, composite examples, NULL handling and dialect differences.
Changing data
Insert, update and delete rows.
- SQL DELETESQL DELETE removes rows from a table based on a WHERE condition. Learn safe deletion habits, deleting with subqueries, and how it differs from TRUNCATE.
- SQL INSERT INTOSQL INSERT INTO adds rows to a table using VALUES. Learn single and multi-row inserts, default values, auto-increment IDs and dialect-specific syntax.
- SQL INSERT INTO SELECTSQL INSERT INTO SELECT copies rows from one table or query into another. See syntax, column matching, filtering and dialect differences with examples.
- SQL UPDATESQL UPDATE changes values in existing rows with SET and a WHERE filter. See syntax, multi-column updates, updates from joins and dialect differences.