liminfoSQL Reference
Free reference guide: SQL Reference
About SQL Reference
The SQL Reference is a comprehensive, searchable quick-reference covering the full spectrum of SQL statements for relational database development. The SELECT section includes basic queries, DISTINCT, WHERE filtering with AND/OR, ORDER BY sorting, LIMIT/OFFSET pagination, pattern matching with LIKE, range conditions with IN/BETWEEN, conditional logic with CASE WHEN, and NULL handling with COALESCE and NULLIF. Each entry provides ready-to-use query examples.
This reference organizes SQL into six categories: SELECT, JOIN, Aggregates, Subqueries, DDL, and Index. The JOIN section covers all join types including INNER JOIN for matching rows, LEFT/RIGHT JOIN for preserving unmatched rows, FULL OUTER JOIN for complete unions, CROSS JOIN for Cartesian products, and SELF JOIN for hierarchical data like employee-manager relationships. The Aggregates section demonstrates COUNT, SUM, AVG, MIN, MAX with GROUP BY and HAVING, plus window functions with RANK and ROW_NUMBER OVER PARTITION BY.
The Subqueries and DDL sections address intermediate-to-advanced SQL patterns. Subqueries include WHERE IN subqueries, EXISTS for correlated existence checks, derived tables for inline views, CTEs (WITH clause) for readable recursive and multi-step queries, and UNION/UNION ALL for combining result sets. The DDL and Index sections cover CREATE TABLE with constraints, ALTER TABLE modifications, INSERT/UPDATE/DELETE/TRUNCATE operations, index creation for query optimization, EXPLAIN for query plan analysis, foreign key relationships, and VIEW definitions for reusable query abstractions.
Key Features
- SELECT queries with DISTINCT, WHERE, ORDER BY, LIMIT/OFFSET, LIKE pattern matching, and CASE WHEN
- All JOIN types: INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF JOIN with practical examples
- Aggregate functions (COUNT, SUM, AVG, MIN, MAX) with GROUP BY, HAVING, and window functions
- Subquery patterns: WHERE IN, EXISTS, derived tables, CTE (WITH clause), and UNION/UNION ALL
- DDL statements: CREATE TABLE with constraints, ALTER TABLE, INSERT, UPDATE, DELETE, and TRUNCATE
- Index management: CREATE INDEX, composite indexes, UNIQUE indexes, and EXPLAIN query plan analysis
- NULL handling with COALESCE for fallback values and NULLIF for conditional null conversion
- Foreign key constraints with ON DELETE CASCADE and VIEW definitions for reusable query abstractions
Frequently Asked Questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows that have matching values in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table; unmatched rows from the right table appear as NULL. Use INNER JOIN when you need only matching records (e.g., orders with their users). Use LEFT JOIN when you need all records from one table regardless of matches (e.g., all users including those with no orders).
How does GROUP BY work with HAVING?
GROUP BY collapses rows with identical values in the specified columns into groups, allowing aggregate functions like COUNT and SUM to operate per group. HAVING filters groups after aggregation, unlike WHERE which filters rows before grouping. For example, SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) >= 10 returns only cities with 10 or more users. Always use WHERE for row-level filters and HAVING for aggregate conditions.
What is the difference between a subquery and a CTE?
A subquery is a query nested inside another query, placed in WHERE, FROM, or SELECT clauses. A CTE (Common Table Expression) uses the WITH keyword to define a named temporary result set before the main query. CTEs are more readable, can be referenced multiple times in the same query, and support recursion. Subqueries can be more concise for simple single-use cases. Performance is typically identical as most databases optimize both the same way.
When should I use EXISTS vs IN for subqueries?
EXISTS returns TRUE as soon as any matching row is found and stops scanning, making it efficient for correlated subqueries checking existence. IN evaluates the complete subquery result set and checks membership. EXISTS is generally faster for large subquery results because it short-circuits. IN is simpler to read and more natural for small value lists. Use EXISTS when checking if related rows exist; use IN when filtering against a specific set of values.
What is the difference between DELETE and TRUNCATE?
DELETE removes rows one by one, supports WHERE conditions for selective deletion, fires triggers, logs individual row deletions, and can be rolled back in a transaction. TRUNCATE removes all rows at once, cannot use WHERE, does not fire row-level triggers, uses minimal logging, and is much faster for clearing entire tables. TRUNCATE also resets auto-increment counters in most databases. Use DELETE for selective removal; TRUNCATE for complete table clearing.
How do indexes improve query performance?
Indexes create a sorted data structure (typically B-tree) that allows the database to find rows without scanning the entire table. A query on an indexed column goes from O(n) full table scan to O(log n) index lookup. Composite indexes (multiple columns) support queries filtering on those columns in order. However, indexes slow down INSERT/UPDATE/DELETE operations and consume storage. Use EXPLAIN to verify that your queries actually use the indexes you create.
What is COALESCE and how does it handle NULL values?
COALESCE returns the first non-NULL value from its argument list. COALESCE(nickname, name, "Anonymous") returns the nickname if it exists, otherwise the name, otherwise "Anonymous". It is SQL standard and works across all databases. Use it for providing fallback values in SELECT and for handling optional columns in WHERE clauses. NULLIF(a, b) returns NULL if a equals b, otherwise returns a, useful for preventing division by zero: amount / NULLIF(count, 0).
What is a VIEW and when should I use one?
A VIEW is a stored SQL query that acts as a virtual table. CREATE VIEW active_users AS SELECT ... lets you query active_users as if it were a real table. Views simplify complex queries, provide a security layer by exposing only specific columns, ensure consistent query logic across applications, and make queries more readable. Views do not store data; they execute the underlying query each time. For materialized (cached) results, use materialized views where supported.