SQL Cheatsheet

This cheatsheet provides a comprehensive and practical reference for SQL commands. It covers data retrieval, manipulation, table operations, constraints, joins, aggregation functions, and advanced SQL patterns. Use it to boost your productivity and master database operations.

143 commands
SELECT

Basic Queries

SELECT * FROM table_name

Select all columns

SELECT col1, col2 FROM table_name

Select specific columns

SELECT DISTINCT col FROM table_name

Select unique values

SELECT * FROM table_name LIMIT 10

Limit results

SELECT * FROM table_name OFFSET 10 LIMIT 10

Pagination

SELECT

WHERE Clause

SELECT * FROM t WHERE col = 'value'

Equal condition

SELECT * FROM t WHERE col != 'value'

Not equal

SELECT * FROM t WHERE col > 10

Greater than

SELECT * FROM t WHERE col BETWEEN 1 AND 10

Range condition

SELECT * FROM t WHERE col IN ('a', 'b', 'c')

Multiple values

SELECT * FROM t WHERE col LIKE 'pat%'

Pattern matching

SELECT * FROM t WHERE col IS NULL

NULL check

SELECT * FROM t WHERE c1 = 'a' AND c2 > 5

Multiple conditions

SELECT

ORDER BY

SELECT * FROM t ORDER BY col ASC

Sort ascending

SELECT * FROM t ORDER BY col DESC

Sort descending

SELECT * FROM t ORDER BY c1 ASC, c2 DESC

Multiple sort columns

SELECT * FROM t ORDER BY col NULLS LAST

NULLs at end

Modify

INSERT

INSERT INTO t (c1, c2) VALUES ('v1', 'v2')

Insert single row

INSERT INTO t VALUES ('v1', 'v2', 'v3')

Insert all columns

INSERT INTO t (c1, c2) VALUES ('a', 'b'), ('c', 'd')

Insert multiple rows

INSERT INTO t SELECT * FROM other_table

Insert from select

INSERT INTO t (c1) VALUES ('v') RETURNING *

Insert and return (PostgreSQL)

Modify

UPDATE

UPDATE t SET col = 'value' WHERE id = 1

Update single column

UPDATE t SET c1 = 'v1', c2 = 'v2' WHERE id = 1

Update multiple columns

UPDATE t SET col = col + 1 WHERE id = 1

Increment value

UPDATE t SET col = NULL WHERE id = 1

Set to NULL

UPDATE t1 SET c1 = t2.c1 FROM t2 WHERE t1.id = t2.id

Update from another table

Modify

DELETE

DELETE FROM t WHERE id = 1

Delete specific rows

DELETE FROM t WHERE col IS NULL

Delete NULL rows

DELETE FROM t

Delete all rows

TRUNCATE TABLE t

Fast delete all (no rollback)

Tables

CREATE TABLE

CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(100))

Create basic table

CREATE TABLE t (id SERIAL PRIMARY KEY, name TEXT NOT NULL)

Auto-increment (PostgreSQL)

CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, name TEXT)

Auto-increment (MySQL)

CREATE TABLE IF NOT EXISTS t (id INT)

Create if not exists

CREATE TABLE t AS SELECT * FROM other_table

Create from query

Tables

ALTER TABLE

ALTER TABLE t ADD COLUMN col VARCHAR(50)

Add column

ALTER TABLE t DROP COLUMN col

Drop column

ALTER TABLE t RENAME COLUMN old TO new

Rename column

ALTER TABLE t ALTER COLUMN col TYPE INT

Change type (PostgreSQL)

ALTER TABLE t MODIFY col INT

Change type (MySQL)

ALTER TABLE t RENAME TO new_name

Rename table

Tables

DROP & TRUNCATE

DROP TABLE t

Delete table

DROP TABLE IF EXISTS t

Delete if exists

DROP TABLE t CASCADE

Delete with dependencies

TRUNCATE TABLE t

Remove all rows

TRUNCATE TABLE t RESTART IDENTITY

Reset auto-increment

JOINs

Join Types

SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id

Inner join - matching rows only

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id

Left join - all from left table

SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.t1_id

Right join - all from right table

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id

Full join - all from both tables

SELECT * FROM t1 CROSS JOIN t2

Cross join - cartesian product

JOINs

Join Variations

SELECT * FROM t1 JOIN t2 USING (col)

Join using common column

SELECT * FROM t1 NATURAL JOIN t2

Natural join - auto match columns

SELECT * FROM t1 a JOIN t1 b ON a.parent_id = b.id

Self join

SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id

Implicit join (old syntax)

💡 INNER JOIN is the default when using just JOIN

Aggregate

Aggregate Functions

SELECT COUNT(*) FROM t

Count all rows

SELECT COUNT(col) FROM t

Count non-NULL values

SELECT COUNT(DISTINCT col) FROM t

Count unique values

SELECT SUM(col) FROM t

Sum of values

SELECT AVG(col) FROM t

Average value

SELECT MIN(col), MAX(col) FROM t

Min and max values

Aggregate

GROUP BY

SELECT col, COUNT(*) FROM t GROUP BY col

Group and count

SELECT c1, c2, SUM(c3) FROM t GROUP BY c1, c2

Multiple group columns

SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 5

Filter groups

SELECT YEAR(date), COUNT(*) FROM t GROUP BY YEAR(date)

Group by expression

Aggregate

String Aggregation

SELECT GROUP_CONCAT(col SEPARATOR ', ') FROM t

Concatenate (MySQL)

SELECT STRING_AGG(col, ', ') FROM t

Concatenate (PostgreSQL)

SELECT ARRAY_AGG(col) FROM t

Aggregate to array (PostgreSQL)

Subqueries

Subquery Types

SELECT * FROM t WHERE col IN (SELECT col FROM t2)

Subquery in WHERE

SELECT * FROM (SELECT * FROM t) AS sub

Subquery in FROM

SELECT col, (SELECT MAX(c) FROM t2) FROM t

Scalar subquery

SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id)

EXISTS subquery

Subqueries

Comparison Operators

SELECT * FROM t WHERE col > ALL (SELECT col FROM t2)

Greater than all

SELECT * FROM t WHERE col > ANY (SELECT col FROM t2)

Greater than any

SELECT * FROM t WHERE col = SOME (SELECT col FROM t2)

Equal to some (same as ANY)

Subqueries

CTE (Common Table Expression)

WITH cte AS (SELECT * FROM t) SELECT * FROM cte

Basic CTE

WITH cte1 AS (...), cte2 AS (...) SELECT * FROM cte1 JOIN cte2

Multiple CTEs

WITH RECURSIVE cte AS (...) SELECT * FROM cte

Recursive CTE

💡 CTEs improve readability and can be referenced multiple times

Constraints

Column Constraints

col INT PRIMARY KEY

Primary key

col VARCHAR(50) NOT NULL

Not null constraint

col VARCHAR(50) UNIQUE

Unique constraint

col INT DEFAULT 0

Default value

col INT CHECK (col > 0)

Check constraint

col INT REFERENCES other_table(id)

Foreign key

Constraints

Table Constraints

PRIMARY KEY (c1, c2)

Composite primary key

UNIQUE (c1, c2)

Composite unique

FOREIGN KEY (col) REFERENCES t(id) ON DELETE CASCADE

FK with cascade delete

FOREIGN KEY (col) REFERENCES t(id) ON UPDATE SET NULL

FK with set null

CHECK (start_date < end_date)

Multi-column check

Constraints

Alter Constraints

ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY (id)

Add primary key

ALTER TABLE t ADD CONSTRAINT fk FOREIGN KEY (col) REFERENCES t2(id)

Add foreign key

ALTER TABLE t DROP CONSTRAINT constraint_name

Drop constraint

Indexes

Create Indexes

CREATE INDEX idx ON t (col)

Create index

CREATE UNIQUE INDEX idx ON t (col)

Unique index

CREATE INDEX idx ON t (c1, c2)

Composite index

CREATE INDEX idx ON t (col DESC)

Descending index

CREATE INDEX idx ON t (col) WHERE condition

Partial index (PostgreSQL)

Indexes

Manage Indexes

DROP INDEX idx

Drop index

DROP INDEX idx ON t

Drop index (MySQL)

REINDEX INDEX idx

Rebuild index (PostgreSQL)

SHOW INDEX FROM t

Show indexes (MySQL)

Views

Create Views

CREATE VIEW v AS SELECT * FROM t WHERE condition

Create view

CREATE OR REPLACE VIEW v AS SELECT ...

Create or replace view

CREATE VIEW v (alias1, alias2) AS SELECT c1, c2 FROM t

View with column aliases

CREATE MATERIALIZED VIEW mv AS SELECT ...

Materialized view (PostgreSQL)

Views

Manage Views

DROP VIEW v

Drop view

DROP VIEW IF EXISTS v

Drop if exists

REFRESH MATERIALIZED VIEW mv

Refresh materialized view

ALTER VIEW v RENAME TO new_name

Rename view

Admin

Database Management

CREATE DATABASE db_name

Create database

DROP DATABASE db_name

Drop database

USE db_name

Switch database (MySQL)

\c db_name

Switch database (PostgreSQL)

SHOW DATABASES

List databases (MySQL)

\l

List databases (PostgreSQL)

Admin

User Management

CREATE USER 'user'@'host' IDENTIFIED BY 'pass'

Create user (MySQL)

CREATE USER user WITH PASSWORD 'pass'

Create user (PostgreSQL)

GRANT ALL ON db.* TO 'user'@'host'

Grant privileges (MySQL)

GRANT ALL ON DATABASE db TO user

Grant privileges (PostgreSQL)

REVOKE ALL ON db.* FROM 'user'@'host'

Revoke privileges

Admin

Table Info

SHOW TABLES

List tables (MySQL)

\dt

List tables (PostgreSQL)

DESCRIBE t

Show table structure (MySQL)

\d t

Show table structure (PostgreSQL)

SHOW CREATE TABLE t

Show CREATE statement

Advanced

Transactions

BEGIN

Start transaction

COMMIT

Commit transaction

ROLLBACK

Rollback transaction

SAVEPOINT sp

Create savepoint

ROLLBACK TO sp

Rollback to savepoint

Advanced

Window Functions

SELECT col, ROW_NUMBER() OVER (ORDER BY col) FROM t

Row number

SELECT col, RANK() OVER (PARTITION BY c1 ORDER BY c2) FROM t

Rank within groups

SELECT col, LAG(col, 1) OVER (ORDER BY id) FROM t

Previous row value

SELECT col, LEAD(col, 1) OVER (ORDER BY id) FROM t

Next row value

SELECT col, SUM(col) OVER (ORDER BY id) FROM t

Running total

Advanced

Set Operations

SELECT * FROM t1 UNION SELECT * FROM t2

Union (no duplicates)

SELECT * FROM t1 UNION ALL SELECT * FROM t2

Union (with duplicates)

SELECT * FROM t1 INTERSECT SELECT * FROM t2

Intersection

SELECT * FROM t1 EXCEPT SELECT * FROM t2

Difference

Advanced

CASE Expression

SELECT CASE WHEN c > 10 THEN 'high' ELSE 'low' END FROM t

Simple CASE

SELECT CASE c WHEN 1 THEN 'one' WHEN 2 THEN 'two' END FROM t

CASE with values

UPDATE t SET c = CASE WHEN x > 0 THEN 1 ELSE 0 END

CASE in UPDATE

Quick Reference

Select all:

SELECT * FROM t

Insert:

INSERT INTO t VALUES

Update:

UPDATE t SET c=v

Delete:

DELETE FROM t WHERE

Categories

  • Data Retrieval

    Commands for querying and retrieving data from tables, including filtering, sorting, and limiting results.

  • Data Modification

    Commands for inserting, updating, and deleting data in tables.

  • Table Operations

    Commands for creating, altering, and managing database tables.

  • Constraints

    Commands for defining and managing data integrity constraints.

  • Joins

    Commands for combining data from multiple tables using various join types.

  • Aggregation Functions

    Commands for performing calculations across sets of rows.

  • Subqueries

    Commands for using queries within other queries for complex operations.

  • Indexes

    Commands for creating and managing indexes to optimize query performance.

  • Transactions

    Commands for managing transactional operations and ensuring data consistency.

  • Views

    Commands for creating and managing virtual tables based on SQL queries.

  • Data Types & Functions

    Common SQL functions for data conversion, manipulation, and calculations.

  • Database Administration

    Commands for managing databases, users, and permissions.

  • Common SQL Patterns

    Advanced SQL patterns and techniques for solving common problems.

Features

  • Quick search functionality
  • Organized by functional categories
  • Clear command descriptions
  • Common SQL syntax compatible with most systems
  • Database-specific variations noted where relevant
  • Easy to copy commands
  • Advanced SQL patterns for real-world problems
  • Responsive design for all devices