SQL vSQL StandardBEGINNERSQL
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
💡 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 (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
💡 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 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 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
💡 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 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
💡 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
💡 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
💡 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
💡 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
💡 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
💡 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
💡 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
💡 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
💡 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
💡 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
💡 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