SQLBEGINNER

SQL

Comprehensive SQL reference covering SELECT, joins, aggregation, CASE expressions, NULL handling, string/date functions, window functions, DDL, transactions, and more.

12 min read

sqldatabasequeriesmysqlpostgresql

SELECT & Filtering

Query data with SELECT, WHERE, LIKE, ORDER BY, and LIMIT.

SELECT Statements

Retrieve data with filtering, sorting, and limiting.

sql

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- WHERE filtering
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE city IN ('NYC', 'LA');
SELECT * FROM users WHERE age BETWEEN 25 AND 35;

-- Sorting
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY age DESC, name ASC;

💡 Use column aliases (AS) to rename output columns — makes results more readable

⚡ LIMIT syntax varies: MySQL/PostgreSQL use LIMIT, SQL Server uses TOP, ANSI uses FETCH

📌 IS NULL is the only way to check for NULL — "= NULL" does not work

🟢 ORDER BY defaults to ASC — only add DESC when you need descending order

selectwhereorder-bylimit

LIKE & Pattern Matching

Filter text with wildcard patterns.

sql

-- % matches any number of characters
SELECT * FROM users WHERE name LIKE 'J%';       -- Starts with J
SELECT * FROM users WHERE email LIKE '%@gmail%'; -- Contains @gmail

-- _ matches exactly one character
SELECT * FROM users WHERE name LIKE '_ohn';      -- ?ohn

-- NOT LIKE
SELECT * FROM users WHERE name NOT LIKE '%test%';

💡 % matches any number of characters (including zero); _ matches exactly one

⚡ PostgreSQL uses ILIKE for case-insensitive matching; MySQL LIKE is case-insensitive by default

📌 LIKE '%term%' cannot use indexes efficiently — consider full-text search for large tables

🟢 Use ESCAPE to match literal % or _ characters in your pattern

likewildcardspattern-matching

Data Modification

Insert, update, and delete data.

INSERT, UPDATE, DELETE

Add, modify, and remove rows.

sql

-- INSERT single row
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@email.com', 30);

-- INSERT multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob', 'bob@email.com'),
  ('Carol', 'carol@email.com');

-- UPDATE
UPDATE users SET age = 31 WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 5;

💡 Always use WHERE with UPDATE and DELETE — without it, every row is affected

⚡ INSERT INTO ... SELECT copies data between tables in one statement

📌 TRUNCATE is faster than DELETE for clearing all rows but cannot be rolled back

🟢 SELECT INTO creates a new table from query results — great for backups

insertupdatedeletedml

Joins

Combine rows from multiple tables with JOIN operations.

JOIN Operations

Inner, outer, self, and cross joins.

sql

-- INNER JOIN — matching rows in both tables
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN — all left rows + matching right
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN — all right rows + matching left
-- FULL OUTER JOIN — all rows from both tables

💡 INNER JOIN returns only matching rows; LEFT JOIN keeps all left-side rows with NULLs for no match

⚡ Self joins are useful for hierarchies — employees/managers, categories/subcategories

📌 Put extra filter conditions in ON (affects join) vs WHERE (filters after join)

🟢 Use table aliases (u, o, p) to keep multi-join queries readable

joinsinnerleftrightself-join

UNION & Combining Results

Combine result sets from multiple queries.

sql

-- UNION — combine and remove duplicates
SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;

-- UNION ALL — combine and keep duplicates (faster)
SELECT city FROM users
UNION ALL
SELECT city FROM offices;

💡 UNION removes duplicates (slower); UNION ALL keeps all rows (faster) — use ALL when you can

⚡ All SELECT statements in a UNION must have the same number of columns with compatible types

📌 ORDER BY goes at the very end and applies to the combined result

🟢 INTERSECT finds common rows; EXCEPT (or MINUS in Oracle) finds rows unique to the first query

unionintersectexceptcombining

Aggregation

Aggregate functions with GROUP BY and HAVING.

GROUP BY & Aggregates

Summarize data with aggregate functions and grouping.

sql

-- Aggregate functions
SELECT COUNT(*) FROM users;
SELECT SUM(amount) FROM orders;
SELECT AVG(age) FROM users;
SELECT MIN(price), MAX(price) FROM products;

-- GROUP BY
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

-- HAVING — filter groups
SELECT city, COUNT(*) AS cnt
FROM users
GROUP BY city
HAVING COUNT(*) > 10;

💡 WHERE filters rows before grouping; HAVING filters groups after aggregation

⚡ COUNT(*) counts all rows; COUNT(column) counts only non-NULL values

📌 Every non-aggregated column in SELECT must appear in GROUP BY

🟢 Execution order: WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

aggregategroup-byhavingcountsum

CASE & NULL Handling

Conditional logic and NULL-safe operations.

CASE Expressions

Add conditional logic (if/else) to queries.

sql

-- CASE WHEN
SELECT name,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age < 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group
FROM users;

-- Simple CASE
SELECT name,
  CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
  END AS status_label
FROM users;

💡 CASE works everywhere: SELECT, WHERE, ORDER BY, UPDATE, and even inside aggregates

⚡ Use CASE inside COUNT/SUM for pivot-style crosstab reports

📌 CASE evaluates conditions in order — the first match wins, then it stops

🟢 Always include ELSE to handle unexpected values — otherwise you get NULL

caseconditionalwhen-then

NULL Handling

Check, replace, and handle NULL values safely.

sql

-- Check for NULL
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- COALESCE — first non-NULL value
SELECT COALESCE(nickname, first_name, 'Unknown') AS display_name
FROM users;

-- NULLIF — returns NULL if values are equal
SELECT NULLIF(discount, 0) FROM products;

💡 COALESCE is standard SQL and works on all databases — prefer it over IFNULL/ISNULL

⚡ NULLIF(x, 0) prevents division-by-zero errors by returning NULL instead

📌 NULL is not equal to anything — even NULL = NULL is false, always use IS NULL

🟢 Aggregate functions (AVG, SUM, COUNT(col)) automatically skip NULL values

nullcoalescenullifis-null

Built-in Functions

String, date, and numeric functions.

String Functions

Manipulate and transform text data.

sql

SELECT UPPER('hello');               -- HELLO
SELECT LOWER('HELLO');               -- hello
SELECT LENGTH('hello');              -- 5
SELECT TRIM('  hello  ');            -- hello
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT SUBSTRING(name, 1, 3) FROM users;  -- First 3 chars
SELECT REPLACE(email, '@old.com', '@new.com') FROM users;

💡 CONCAT works across databases; || is PostgreSQL/SQLite only, + is SQL Server only

⚡ SUBSTRING(col, start, length) — start position is 1-based, not 0-based

📌 Function names vary: LENGTH (MySQL/PG) vs LEN (SQL Server), CHARINDEX vs POSITION

🟢 LPAD with zeros is a common trick for formatting IDs: LPAD(id, 5, '0') → 00042

stringsfunctionsconcatsubstring

Date & Numeric Functions

Work with dates, times, and numbers.

sql

-- Current date/time
SELECT NOW();                    -- Current timestamp
SELECT CURRENT_DATE;             -- Current date
SELECT CURRENT_TIMESTAMP;        -- ANSI standard

-- Extract parts
SELECT EXTRACT(YEAR FROM created_at) FROM orders;
SELECT EXTRACT(MONTH FROM created_at) FROM orders;

-- Date arithmetic
SELECT created_at + INTERVAL '30 days' FROM orders;

💡 Date functions vary the most across databases — ANSI EXTRACT works on most

⚡ DATE_TRUNC is incredibly useful for grouping by month/week/year in reports

📌 ROUND(value, 2) rounds to 2 decimal places — essential for financial calculations

🟢 Use CURRENT_DATE and CURRENT_TIMESTAMP for portable ANSI-standard date/time

datesnumericfunctionsextract

Subqueries & CTEs

Nest queries and organize complex logic with Common Table Expressions.

Subqueries & CTEs

Write nested queries and reusable named expressions.

sql

-- Subquery in WHERE
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- EXISTS
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- CTE (Common Table Expression)
WITH active_users AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE age > 25;

💡 CTEs make complex queries readable — define named blocks with WITH, then reference them

⚡ EXISTS is often faster than IN for large subqueries — it stops at the first match

📌 Correlated subqueries run once per outer row — they can be slow on large tables

🟢 Recursive CTEs are the standard way to query hierarchical data (org charts, categories)

subqueriescteexistsrecursive

Window Functions

Perform calculations across related rows without collapsing groups.

Window Functions

ROW_NUMBER, RANK, LAG, LEAD, running totals, and partitioned aggregates.

sql

-- ROW_NUMBER
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- RANK with PARTITION BY
SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- Running total
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM payments;

💡 Window functions compute across rows without collapsing them — unlike GROUP BY

⚡ ROW_NUMBER + CTE is the standard pattern for "top N per group" queries

📌 RANK has gaps after ties (1,2,2,4); DENSE_RANK has no gaps (1,2,2,3)

🟢 LAG/LEAD let you compare each row to its previous/next row — great for trend analysis

windowrow-numberranklaglead

Table Management

Create, alter, and drop tables with proper data types.

DDL & Data Types

Define tables with CREATE, ALTER, DROP and choose the right data types.

sql

-- CREATE TABLE
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  age INT CHECK (age >= 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ALTER TABLE
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN name TO full_name;

-- DROP TABLE
DROP TABLE IF EXISTS temp_data;

💡 Auto-increment syntax varies: AUTO_INCREMENT (MySQL), SERIAL (PG), IDENTITY (SQL Server)

⚡ Use DECIMAL(10,2) for money — never use FLOAT for financial data

📌 VARCHAR(255) is a safe default for most string columns; use TEXT for unlimited length

🟢 Always use DROP TABLE IF EXISTS to avoid errors in scripts and migrations

ddlcreate-tabledata-typesalter

Constraints & Keys

Enforce data integrity with primary keys, foreign keys, and constraints.

Constraints

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT.

sql

-- Primary key
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL
);

-- Foreign key
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
);

-- Add constraint to existing table
ALTER TABLE users ADD CONSTRAINT age_check CHECK (age >= 0);

💡 ON DELETE CASCADE automatically deletes child rows — use carefully

⚡ Composite primary keys use multiple columns — common in junction/pivot tables

📌 RESTRICT is the default ON DELETE behavior — it prevents deleting referenced rows

🟢 Name your constraints (ADD CONSTRAINT name) — makes them easier to drop later

constraintsprimary-keyforeign-keyunique

Indexes & Performance

Create indexes to speed up queries.

Indexes

Create and manage indexes for query performance.

sql

-- Create index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_users_name_age ON users(name, age);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Drop index
DROP INDEX idx_users_email;

💡 Composite index column order matters — (name, age) helps "WHERE name = ..." but not "WHERE age = ..."

⚡ Use EXPLAIN ANALYZE to see if your query actually uses the index

📌 Indexes speed up reads but slow down writes — don't over-index

🟢 Always index foreign key columns — JOINs on unindexed FKs are very slow

indexesperformanceexplain

Transactions

Group operations into atomic units with COMMIT and ROLLBACK.

Transaction Control

BEGIN, COMMIT, ROLLBACK, and savepoints.

sql

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
-- or ROLLBACK; to undo

💡 Transactions are atomic — either all operations succeed (COMMIT) or none do (ROLLBACK)

⚡ Use savepoints for partial rollbacks without losing the entire transaction

📌 Always COMMIT or ROLLBACK — uncommitted transactions hold locks and block other queries

🟢 Most databases auto-commit individual statements — BEGIN is needed for multi-statement atomicity

transactionscommitrollbacksavepoint

Views & Stored Procedures

Create reusable views and stored procedures.

Views

Save queries as reusable virtual tables.

sql

-- Create view
CREATE VIEW active_users AS
SELECT id, name, email
FROM users WHERE status = 'active';

-- Use it like a table
SELECT * FROM active_users WHERE age > 25;

-- Replace existing view
CREATE OR REPLACE VIEW user_summary AS
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Drop view
DROP VIEW IF EXISTS active_users;

💡 Views simplify complex queries — define once, use everywhere like a table

⚡ Materialized views (PostgreSQL) cache results for faster reads — great for dashboards

📌 Regular views re-run the query each time; materialized views store the result

🟢 Views are great for access control — expose only certain columns to certain users

viewsmaterialized-views

Stored Procedures

Save reusable blocks of SQL logic on the database server.

sql

-- Create procedure (MySQL)
DELIMITER //
CREATE PROCEDURE get_user_orders(IN user_id INT)
BEGIN
  SELECT * FROM orders WHERE user_id = user_id;
END //
DELIMITER ;

-- Call it
CALL get_user_orders(42);

💡 Syntax varies significantly: MySQL uses DELIMITER, PostgreSQL uses $$ blocks, SQL Server uses @params

⚡ Stored procedures run on the server — reduce network round trips for complex operations

📌 PostgreSQL uses CREATE FUNCTION (not PROCEDURE) for most use cases

🟢 Use stored procedures for operations that should always run the same logic regardless of client

stored-proceduresfunctionsplpgsql

SQL

Table of Contents

SELECT & Filtering

SELECT Statements

LIKE & Pattern Matching

Data Modification

INSERT, UPDATE, DELETE

Joins

JOIN Operations

UNION & Combining Results

Aggregation

GROUP BY & Aggregates

CASE & NULL Handling

CASE Expressions

NULL Handling

Built-in Functions

String Functions

Date & Numeric Functions

Subqueries & CTEs

Subqueries & CTEs

Window Functions

Window Functions

Table Management

DDL & Data Types

Constraints & Keys

Constraints

Indexes & Performance

Indexes

Transactions

Transaction Control

Views & Stored Procedures

Views

Stored Procedures