SQL Cheatsheet

Primary Key

Uniquely identifies each record in a table. Only one primary key allowed.

- Column(s) must be unique - Cannot contain null values - Limited to one per table

CREATE TABLE table_name ( id INT PRIMARY KEY );

Foreign Key

Links to primary key in another table. Enforces referential integrity.

- Column(s) must match type of referenced column(s) - Values must exist in referenced table

CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id) );

Unique

Ensures values in column are unique. Only distinct values allowed.

- Column(s) cannot contain duplicate values

CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(50) UNIQUE );

Not Null

Mandates column cannot contain NULL values.

- Column cannot contain null values

CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL );

Check

Validates values against logical expression.

- Inserted/updated values must evaluate to true

CREATE TABLE users ( age INT CHECK (age > 0) );

Default

Sets default value if none provided.

- Default value is inserted if no other value specified

CREATE TABLE users ( country VARCHAR(50) DEFAULT 'United States' );

Index

Improves lookup performance on columns.

- Can be unique or non-unique - Single column or multiple columns

CREATE INDEX idx_lastname ON users(lastname);

INNER JOIN

Returns records that have matching values in both tables

Matches rows from the first table with rows from the second table where the join condition is met

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

LEFT JOIN

Returns all records from the left table, and the matched records from the right table

Returns all rows from the left table, along with any matching rows from the right table

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

RIGHT JOIN

Returns all records from the right table, and the matched records from the left table

Returns all rows from the right table, along with any matching rows from the left table

SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id

FULL OUTER JOIN

Returns all records when there is a match in either left or right table

Returns rows when there is a match in one of the tables

SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id

CROSS JOIN

Returns rows combined by cartesian product between two tables

No join condition needed, combines every row from the first table with every row from the second table

SELECT * FROM table1 CROSS JOIN table2

GROUP BY

Used to group rows that have the same values into summary rows

Used after the FROM and WHERE clauses. Must be used with aggregate functions like COUNT, MAX etc

SELECT department, COUNT(*) FROM employees GROUP BY department

GROUP BY Column

Groups rows based on values in the specified column

Column used in GROUP BY must be present in the SELECT statement

SELECT department, COUNT(*) FROM employees GROUP BY department

GROUP BY Multiple Columns

Groups rows based on multiple column values

List multiple columns in GROUP BY to group on combinations of values

SELECT department, city, COUNT(*) FROM employees GROUP BY department, city

HAVING

Filters grouped rows that meet a specified condition

Goes after GROUP BY. Can reference aggregated columns

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10

ROLLUP

Provides subtotals at different grouping levels

Used after GROUP BY, provides extra super-aggregate rows

SELECT year, quarter, SUM(profit) FROM sales GROUP BY year, quarter WITH ROLLUP