Explore SQL query execution step by step. Learn what each clause does, how databases process it, and why the order matters.
Understanding this distinction makes complex queries much easier to reason about.
The SELECT clause defines which columns or expressions you want in the final result.
SELECT name, salary
FROM employees;FROM identifies the source table or tables from which the database reads data.
SELECT determines which columns or expressions appear in the final result.
The database returns a new result set containing the requested values.
WHERE filters individual rows before they reach the final result.
SELECT name, salary
FROM employees
WHERE salary > 50000;The condition is evaluated against rows from the source data.
Rows for which the condition evaluates to true continue through the query.
Rows that do not satisfy the condition are excluded.
DISTINCT removes duplicate combinations from the selected result.
SELECT DISTINCT department
FROM employees;Repeated values can appear multiple times in the result.
Duplicate result combinations are eliminated.
ORDER BY sorts the rows in the result according to one or more expressions.
SELECT name, salary
FROM employees
ORDER BY salary DESC;Sorts values in ascending order.
Sorts values in descending order.
You can specify multiple ordering expressions to define secondary sorting.
LIMIT restricts how many rows are returned by the query.
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;GROUP BY divides rows into groups that can be summarized with aggregate functions.
SELECT department, COUNT(*)
FROM employees
GROUP BY department;The GROUP BY expression determines which rows belong together.
Functions such as COUNT, SUM, AVG, MIN and MAX can summarize each group.
The result normally contains one output row for each group.
HAVING filters groups after GROUP BY and aggregation.
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;JOIN combines rows from multiple tables using a relationship between their columns.
SELECT
employees.name,
departments.department_name
FROM employees
JOIN departments
ON employees.department = departments.department_name;Aggregate functions calculate a value from a set of rows.
SELECT
COUNT(*) AS employees,
AVG(salary) AS average_salary,
MAX(salary) AS highest_salary
FROM employees;CASE creates conditional values inside a query.
SELECT
name,
salary,
CASE
WHEN salary >= 70000 THEN 'Senior'
WHEN salary >= 50000 THEN 'Mid'
ELSE 'Junior'
END AS level
FROM employees;A subquery is a query nested inside another SQL statement.
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);Produces a value or result used by the outer query.
Uses the subquery result to complete the larger operation.
A CTE gives a temporary name to a query result so it can be referenced by the main statement.
WITH high_earners AS (
SELECT *
FROM employees
WHERE salary > 70000
)
SELECT name, salary
FROM high_earners;Window functions calculate across related rows while keeping individual result rows.
SELECT
name,
department,
salary,
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS department_rank
FROM employees;Divides rows into independent groups for the window operation.
Defines the ordering used by the window calculation.
Window functions do not collapse the original rows into one row per group.
INSERT adds new rows to a table.
INSERT INTO employees
(name, department, salary)
VALUES
('Aman', 'Engineering', 65000);UPDATE changes values in existing rows.
UPDATE employees
SET salary = 70000
WHERE employee_id = 5;DELETE removes rows from a table.
DELETE FROM employees
WHERE employee_id = 5;CREATE TABLE defines a new table and its columns.
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
name TEXT,
department TEXT,
salary INTEGER
);Define the data fields that rows can contain.
Define the kind of values that each column can store.
PRIMARY KEY, FOREIGN KEY and other constraints define rules around the data.
ALTER TABLE changes the structure of an existing table.
ALTER TABLE employees
ADD COLUMN email TEXT;DROP TABLE removes a table definition from the database.
DROP TABLE employees;NULL represents the absence of a value and requires special SQL handling.
SELECT *
FROM employees
WHERE department IS NULL;Use IS NULL when looking for missing values.
Use IS NOT NULL when you need rows containing a value.
NULL does not behave like an ordinary value in comparisons.
A query is not magic. It is a sequence of operations that transforms source data into a result.
Understand SQL syntax
Find tables and data
Remove rows that do not match
Join or group data
Produce requested columns
Arrange the result
Send rows back