Anatomy of a SQL query. SELECT, WHERE, JOINs, ORDER BY — the base everything else builds on.
You write SQL in one order. The database runs it in a different order. This is the source of most beginner errors.
-- You write:
SELECT name, dept, salary
FROM employees
WHERE salary > 70000
ORDER BY salary DESC;
-- DB runs in this order:
-- 1. FROM → which table(s)
-- 2. WHERE → filter rows
-- 3. SELECT → pick columns
-- 4. ORDER BY → sort results-- Avoid SELECT * in production — pick columns explicitly
SELECT name, salary, dept
FROM employees;
-- Aliases make output readable
SELECT name AS employee_name,
salary * 12 AS annual_salary,
dept AS department
FROM employees;-- Comparison operators
WHERE salary > 70000
WHERE dept = 'IT'
WHERE salary BETWEEN 60000 AND 80000
-- IN — cleaner than multiple ORs
WHERE city IN ('Mumbai', 'Delhi', 'Pune')
-- LIKE — pattern matching
WHERE name LIKE 'A%' -- starts with A
WHERE name LIKE '%Kumar' -- ends with Kumar
-- NULL — NEVER use = NULL
WHERE manager_id IS NULL -- correct ✓
WHERE manager_id = NULL -- wrong ✗ returns nothing(dept='IT' OR dept='Finance') AND city='Mumbai'-- Sort descending, then by name for ties
SELECT name, salary FROM employees
ORDER BY salary DESC, name ASC;
-- Top 3 rows
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 3; -- MySQL / PostgreSQL
FETCH FIRST 3 ROWS ONLY; -- Oracle 12c+JOINs combine rows from two tables on a matching column.
| Join Type | Returns | Use when |
|---|---|---|
| INNER JOIN | Only matching rows from both tables | You need data from both sides |
| LEFT JOIN | All left rows + matches from right (NULL if no match) | Keep all records from main table |
| RIGHT JOIN | All right rows + matches from left | Rarely used — flip to LEFT instead |
-- INNER JOIN — only employees who have a matching department
SELECT e.name, e.salary, d.dept_name, d.budget
FROM employees e
INNER JOIN departments d ON e.dept = d.dept_name;
-- LEFT JOIN — all employees, dept info where available
SELECT e.name, d.budget
FROM employees e
LEFT JOIN departments d ON e.dept = d.dept_name;
-- Find departments with NO employees
SELECT d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept = d.dept_name
WHERE e.emp_id IS NULL;
-- Multi-table join: employee + department + manager name
SELECT e.name, d.dept_name, m.name AS manager
FROM employees e
LEFT JOIN departments d ON e.dept = d.dept_name
LEFT JOIN employees m ON e.manager_id = m.emp_id;