Complete SQL Guide: From Basic Queries to Advanced Optimization

By Lisa Park Jul 12, 2026 18 min read Programming

SQL has been around for over 50 years and remains the most important language for working with data. Whether you are a backend developer, data analyst, or data scientist, SQL is fundamental. And no, it is not going away.

Level 1: SELECT, WHERE, ORDER BY, LIMIT

SELECT is the most basic query. SELECT * FROM usuarios brings all columns. SELECT nombre, email FROM usuarios brings only what you need. Always specify columns instead of using * in production. WHERE filters rows with operators like =, <>, <, >, LIKE, IN, BETWEEN, IS NULL.

ORDER BY sorts results ascending (ASC) or descending (DESC). LIMIT and OFFSET control how many rows to return. LIMIT 10 OFFSET 20 skips the first 20 and returns 10 rows. Useful for pagination but OFFSET can be slow on large tables.

Level 2: JOINs

INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table and matches from the right (NULL if no match). RIGHT JOIN is the opposite. FULL OUTER JOIN returns all rows from both tables.

Syntax: SELECT u.name, p.title FROM users u LEFT JOIN posts p ON u.id = p.user_id. Always use aliases and qualify column names to avoid ambiguity.

Level 3: Aggregation and GROUP BY

COUNT, SUM, AVG, MAX, MIN are aggregate functions. GROUP BY groups rows before aggregating. SELECT category_id, COUNT(*) as total, AVG(price) as avg_price FROM products GROUP BY category_id. HAVING filters groups after aggregation, WHERE filters before.

Level 4: Subqueries and CTEs

CTEs with WITH make queries more readable. WITH active_orders AS (SELECT * FROM orders WHERE status = 'active'), totals AS (SELECT user_id, COUNT(*) FROM active_orders GROUP BY user_id) SELECT * FROM totals WHERE total > 5.

Level 5: Window Functions

Window functions calculate values across related rows without grouping them. ROW_NUMBER(), RANK(), DENSE_RANK() assign numbers within a partition. SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as ranking FROM employees.

PARTITION BY divides rows into groups. LAG and LEAD access previous or next rows. SUM() OVER (ORDER BY date) gives running totals.

Level 6: Indexes and Optimization

Indexes make queries faster by avoiding full table scans. CREATE INDEX idx_users_email ON users(email). But indexes slow writes. Use EXPLAIN ANALYZE to see if queries use indexes. Composite indexes on multiple columns work for queries filtering by the first column, or first + second, but not second alone.