SQL Fundamentals

What this lesson covers

SQL (Structured Query Language) is the language you use to define, change and query data in a relational database. It is declarative: you describe the result you want, and the database works out how to produce it. SQL is the single most tested database skill in interviews, from service-company aptitude rounds to product-company coding rounds, and nearly every backend job uses it daily.

Interviewers check three things. First, whether you can write correct queries for ordinary questions: filtering, grouping, joining. Second, whether you know the traps: NULL comparisons, NOT IN with NULLs, filtering an outer join in the wrong place, WHERE versus HAVING. Third, whether you understand what the database does: the logical order in which clauses run, and the difference between DELETE, TRUNCATE and DROP.

Every query in this lesson was run in SQLite 3.51, and the result tables shown are the actual output. Where MySQL, PostgreSQL, SQL Server or Oracle behave differently, the text says so. Advanced SQL continues with window functions, CTEs and classic interview problems.

The sample dataset

Two small tables are used throughout. Keep them in view; every result below can be checked by hand against them.

CREATE TABLE dept (
  dept_id   INTEGER PRIMARY KEY,
  dept_name TEXT NOT NULL UNIQUE
);

CREATE TABLE emp (
  emp_id     INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  dept_id    INTEGER REFERENCES dept(dept_id),
  manager_id INTEGER REFERENCES emp(emp_id),
  salary     INTEGER NOT NULL CHECK (salary > 0),
  city       TEXT
);

INSERT INTO dept VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'HR');
INSERT INTO emp VALUES
  (1, 'Asha',  1,    NULL, 90000, 'Bengaluru'),
  (2, 'Ravi',  1,    1,    70000, 'Hyderabad'),
  (3, 'Meera', 2,    1,    60000, 'Bengaluru'),
  (4, 'Kiran', NULL, 2,    40000, NULL),
  (5, 'Dev',   2,    3,    55000, 'Pune');

dept                         emp
+---------+-------------+    +----+-------+------+-----+--------+-----------+
| dept_id | dept_name   |    | id | name  | dept | mgr | salary | city      |
+---------+-------------+    +----+-------+------+-----+--------+-----------+
| 1       | Engineering |    | 1  | Asha  | 1    |     | 90000  | Bengaluru |
| 2       | Sales       |    | 2  | Ravi  | 1    | 1   | 70000  | Hyderabad |
| 3       | HR          |    | 3  | Meera | 2    | 1   | 60000  | Bengaluru |
+---------+-------------+    | 4  | Kiran |      | 2   | 40000  |           |
                             | 5  | Dev   | 2    | 3   | 55000  | Pune      |
                             +----+-------+------+-----+--------+-----------+

(Blank cells are NULL.) Two details matter later: Kiran has no department, and HR has no employees. Those two rows make the different join types produce different results.

The four sub-languages

SQL statements fall into groups by purpose.

Some books separate SELECT into DQL (Data Query Language). One practical difference: in Oracle and MySQL, DDL statements cause an implicit commit, so you cannot roll back a CREATE TABLE or TRUNCATE. PostgreSQL and SQLite let most DDL run inside a transaction and be rolled back.

DDL: creating tables with constraints

CREATE TABLE defines columns, their types and their constraints (rules the database enforces on every write).

Constraints can be written next to a column (column-level) or after all columns (table-level). Multi-column keys must be table-level:

CREATE TABLE timesheet (
  emp_id    INTEGER NOT NULL REFERENCES emp(emp_id) ON DELETE CASCADE,
  work_date TEXT    NOT NULL,
  hours     REAL    NOT NULL DEFAULT 8 CHECK (hours > 0 AND hours <= 24),
  CONSTRAINT pk_timesheet PRIMARY KEY (emp_id, work_date)
);

Naming a constraint (CONSTRAINT pk_timesheet) makes error messages clearer and lets you drop it later by name.

ALTER TABLE changes an existing table:

ALTER TABLE emp ADD COLUMN email TEXT;
ALTER TABLE emp RENAME COLUMN email TO work_email;
ALTER TABLE emp DROP COLUMN work_email;

SQLite supports these three forms but not, for example, ALTER COLUMN ... TYPE or adding a constraint to an existing table; PostgreSQL and MySQL support much more (ALTER TABLE emp ALTER COLUMN salary TYPE BIGINT in PostgreSQL, MODIFY COLUMN in MySQL).

DML: INSERT, UPDATE, DELETE

-- Insert: name the columns so the statement survives schema changes
INSERT INTO dept (dept_id, dept_name) VALUES (4, 'Finance');

-- Insert from a query
CREATE TABLE high_earners (emp_id INTEGER, name TEXT);
INSERT INTO high_earners (emp_id, name)
SELECT emp_id, name FROM emp WHERE salary >= 70000;

-- Update: always think about the WHERE clause first
UPDATE emp SET salary = salary + 5000 WHERE dept_id = 2;

-- Delete specific rows
DELETE FROM dept WHERE dept_id = 4;

-- Put Sales salaries back so later examples match the table above
UPDATE emp SET salary = salary - 5000 WHERE dept_id = 2;

An UPDATE or DELETE without WHERE affects every row. Many teams run such statements inside a transaction first, check the reported row count, then commit:

BEGIN;
UPDATE emp SET city = 'Chennai' WHERE emp_id = 5;
-- check: SELECT * FROM emp WHERE emp_id = 5;
ROLLBACK;   -- or COMMIT if it looks right

An upsert inserts a row or updates it if the key already exists. Syntax differs by database: INSERT ... ON CONFLICT (key) DO UPDATE SET ... in PostgreSQL and SQLite, INSERT ... ON DUPLICATE KEY UPDATE ... in MySQL, and MERGE in SQL Server and Oracle.

The anatomy of SELECT

A full query can have these clauses, and they must be written in this order:

SELECT   DISTINCT column_list
FROM     table_list  JOIN ... ON ...
WHERE    row_condition
GROUP BY grouping_columns
HAVING   group_condition
ORDER BY sort_columns
LIMIT    n OFFSET m;

Logical order of execution

The database evaluates them in a different order. This logical order explains most "why does this not work?" errors.

 1. FROM / JOIN   build the combined set of rows
 2. WHERE         drop rows that fail the condition
 3. GROUP BY      put remaining rows into groups
 4. HAVING        drop groups that fail the condition
 5. SELECT        compute output columns, aggregates, aliases
 6. DISTINCT      remove duplicate output rows
 7. ORDER BY      sort
 8. LIMIT/OFFSET  keep a slice of the sorted rows

Consequences you can reason out from this list:

• WHERE cannot use aggregates. At step 2 there are no groups yet, so WHERE COUNT(*) > 2 is an error. Use HAVING.
• WHERE cannot use a SELECT alias in standard SQL, because aliases are created at step 5. SELECT salary * 12 AS annual FROM emp WHERE annual > 600000 fails in PostgreSQL and SQL Server. (SQLite and MySQL are lenient and allow some alias use, but do not rely on it.)
• ORDER BY can use aliases, because it runs after SELECT.
• LIMIT applies last, so ORDER BY decides which rows you keep.

This is the logical order. The optimizer may physically do things differently, for example use an index to read rows already sorted, as long as the result is the same.

Filtering with WHERE

Comparison and logical operators

=, <> (or !=), <, >, <=, >=, combined with AND, OR, NOT. AND binds tighter than OR, so use parentheses: WHERE dept_id = 1 OR dept_id = 2 AND salary > 60000 means dept_id = 1 OR (dept_id = 2 AND salary > 60000).

BETWEEN

BETWEEN a AND b is inclusive at both ends: it means >= a AND <= b.

SELECT name, salary FROM emp WHERE salary BETWEEN 50000 AND 70000;

name  | salary
------+-------
Ravi  | 70000
Meera | 60000
Dev   | 55000

Ravi's 70000 is included. With dates and timestamps, prefer >= start AND < next_day, because BETWEEN '2026-01-01' AND '2026-01-31' on a timestamp column misses everything after midnight on the 31st.

IN

IN (list) is shorthand for several OR equalities.

SELECT name FROM emp WHERE city IN ('Pune', 'Hyderabad');

Result: Ravi, Dev.

LIKE

LIKE matches a pattern. % matches any sequence of characters (including none); _ matches exactly one character.

SELECT name FROM emp WHERE name LIKE '_a%';   -- second letter is 'a'

Result: Ravi. (Asha's second letter is s, Kiran's is i.)

Case sensitivity differs: SQLite's LIKE is case-insensitive for ASCII letters, MySQL depends on the column's collation (usually case-insensitive), and PostgreSQL's LIKE is case-sensitive (use ILIKE for case-insensitive). A pattern starting with % cannot use a normal B-tree index, so LIKE '%term' scans the table; see indexing and B-trees.

NULL and three-valued logic

NULL means "unknown" or "not applicable". It is not zero, not an empty string, and not a value at all. This single idea causes more wrong answers than anything else in SQL.

Any comparison with NULL gives UNKNOWN, not TRUE or FALSE. SQL therefore uses three-valued logic: TRUE, FALSE and UNKNOWN. WHERE keeps a row only if the condition is TRUE; UNKNOWN rows are dropped, just like FALSE ones.

 NOT       AND  | T  F  U        OR   | T  F  U
 T -> F    -----+---------       -----+--------
 F -> T     T   | T  F  U         T   | T  T  T
 U -> U     F   | F  F  F         F   | T  F  U
            U   | U  F  U         U   | T  U  U

Read it like this: FALSE AND anything is FALSE; TRUE OR anything is TRUE; otherwise UNKNOWN spreads.

Tested in SQLite (which shows UNKNOWN as NULL, and TRUE/FALSE as 1/0):

Consequences

= NULL never matches anything. Use IS NULL / IS NOT NULL.

SELECT name FROM emp WHERE city = NULL;    -- returns no rows
SELECT name FROM emp WHERE city IS NULL;   -- Kiran

Negative conditions silently skip NULLs:

SELECT name FROM emp WHERE city <> 'Pune';

Result: Asha, Ravi, Meera. Kiran is missing, because NULL <> 'Pune' is UNKNOWN. If you want her, write WHERE city <> 'Pune' OR city IS NULL, or in PostgreSQL city IS DISTINCT FROM 'Pune'.

Aggregates ignore NULLs, except COUNT(*):

SELECT COUNT(*), COUNT(city), COUNT(dept_id), COUNT(DISTINCT city) FROM emp;

COUNT(*) | COUNT(city) | COUNT(dept_id) | COUNT(DISTINCT city)
---------+-------------+----------------+---------------------
5        | 4           | 4              | 3

COUNT(*) counts rows; COUNT(col) counts non-NULL values. Similarly, AVG(col) divides by the number of non-NULL values, which is not always what you want.

Other NULL rules:

• Arithmetic with NULL gives NULL: salary + NULL is NULL.
• COALESCE(a, b, c) returns the first non-NULL argument: COALESCE(city, 'Unknown').
• NULLIF(a, b) returns NULL if a = b, else a; handy for avoiding division by zero: x / NULLIF(y, 0).
• For GROUP BY, DISTINCT, UNION and INTERSECT, NULLs are treated as equal to each other, so all NULLs form one group.
• In ORDER BY, NULLs sort first in SQLite, MySQL and SQL Server (ascending), and last in PostgreSQL and Oracle. Use NULLS FIRST / NULLS LAST (PostgreSQL, Oracle, SQLite 3.30+) to be explicit.

Sorting and paging: ORDER BY, LIMIT, OFFSET

ORDER BY sorts by one or more columns, each ASC (default) or DESC. Without ORDER BY, row order is not guaranteed, even if it looks stable today.

SELECT name, city FROM emp ORDER BY city;

name  | city
------+----------
Kiran | NULL
Asha  | Bengaluru
Meera | Bengaluru
Ravi  | Hyderabad
Dev   | Pune

Kiran's NULL sorts first in SQLite. Asha and Meera tie on city; their relative order is not guaranteed unless you add a tie-breaker like ORDER BY city, name.

LIMIT n OFFSET m skips m rows and returns the next n:

SELECT name, salary FROM emp ORDER BY salary DESC LIMIT 2 OFFSET 1;

name  | salary
------+-------
Ravi  | 70000
Meera | 60000

Sorted salaries are 90000, 70000, 60000, 55000, 40000; skip one, take two. Syntax varies: LIMIT/OFFSET in MySQL, PostgreSQL and SQLite; OFFSET m ROWS FETCH NEXT n ROWS ONLY in the SQL standard, SQL Server 2012+ and Oracle 12c+; TOP n in SQL Server.

Large offsets are slow because the database still produces and discards the skipped rows. For deep pagination use keyset pagination: remember the last value seen and ask for WHERE salary < :last_salary ORDER BY salary DESC LIMIT 20 (with a unique tie-breaker column in practice).

Aggregates, GROUP BY and HAVING

Aggregate functions compute one value from many rows: COUNT, SUM, AVG, MIN, MAX. Without GROUP BY, they summarize the whole table into one row.

GROUP BY splits the rows into groups that share the same values in the listed columns, and computes the aggregates per group.

SELECT dept_id,
       COUNT(*)    AS n,
       SUM(salary) AS total,
       AVG(salary) AS avg_sal,
       MAX(salary) AS max_sal
FROM emp
GROUP BY dept_id;

dept_id | n | total  | avg_sal | max_sal
--------+---+--------+---------+--------
NULL    | 1 | 40000  | 40000.0 | 40000
1       | 2 | 160000 | 80000.0 | 90000
2       | 2 | 115000 | 57500.0 | 60000

Check by hand: Engineering is Asha 90000 + Ravi 70000 = 160000, average 80000. Sales is Meera 60000 + Dev 55000 = 115000, average 57500. Kiran's NULL forms its own group.

The rule for SELECT with GROUP BY: every column in SELECT must either be in GROUP BY or be inside an aggregate. SELECT dept_id, name, COUNT(*) FROM emp GROUP BY dept_id is an error in PostgreSQL and SQL Server, and in MySQL with the default ONLY_FULL_GROUP_BY mode, because each group has several names and the database cannot know which one you mean. SQLite accepts it and returns a value from an arbitrary row of the group; never rely on that.

WHERE versus HAVING

WHERE filters rows before grouping. HAVING filters groups after grouping and can use aggregates.

"Departments with at least two employees earning more than 50000":

SELECT dept_id, COUNT(*) AS n
FROM emp
WHERE salary > 50000           -- row filter: drops Kiran (40000)
GROUP BY dept_id
HAVING COUNT(*) >= 2;          -- group filter

dept_id | n
--------+--
1       | 2
2       | 2

Step by step: WHERE removes Kiran, leaving Asha, Ravi (dept 1) and Meera, Dev (dept 2). Grouping gives two groups of two. Both pass HAVING.

If a condition does not involve an aggregate, put it in WHERE: filtering early means less data to group.

Joins

A join combines rows from two tables based on a related column. The sample data was chosen so that every join type gives a different result: Kiran has no department, and HR has no employees.

 emp (left)                      dept (right)
 Asha  -> 1  ---------------->   1 Engineering
 Ravi  -> 1  ----------------^
 Meera -> 2  ---------------->   2 Sales
 Dev   -> 2  ----------------^
 Kiran -> NULL  (no match)       3 HR   (no match)

INNER JOIN

Returns only pairs that match on both sides. JOIN alone means INNER JOIN.

SELECT e.name, d.dept_name
FROM emp e
INNER JOIN dept d ON e.dept_id = d.dept_id;

name  | dept_name
------+------------
Asha  | Engineering
Ravi  | Engineering
Meera | Sales
Dev   | Sales

Kiran (no department) and HR (no employees) both disappear.

LEFT (OUTER) JOIN

Returns every row from the left table; where there is no match, the right side's columns are NULL.

SELECT e.name, d.dept_name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id;

name  | dept_name
------+------------
Asha  | Engineering
Ravi  | Engineering
Meera | Sales
Kiran | NULL
Dev   | Sales

RIGHT (OUTER) JOIN

Returns every row from the right table. It is a left join with the tables swapped, so many teams only ever write left joins. SQLite supports RIGHT and FULL joins from version 3.39 (2022); older SQLite did not.

SELECT e.name, d.dept_name
FROM emp e
RIGHT JOIN dept d ON e.dept_id = d.dept_id;

name  | dept_name
------+------------
Asha  | Engineering
Ravi  | Engineering
Meera | Sales
Dev   | Sales
NULL  | HR

FULL (OUTER) JOIN

Returns every row from both tables, matched where possible.

SELECT e.name, d.dept_name
FROM emp e
FULL OUTER JOIN dept d ON e.dept_id = d.dept_id;

name  | dept_name
------+------------
Asha  | Engineering
Ravi  | Engineering
Meera | Sales
Kiran | NULL
Dev   | Sales
NULL  | HR

MySQL has no FULL OUTER JOIN. Emulate it with a left join UNION a right join (use UNION ALL with an IS NULL filter on the second part if duplicate rows are possible):

SELECT e.name, d.dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_id
UNION
SELECT e.name, d.dept_name FROM emp e RIGHT JOIN dept d ON e.dept_id = d.dept_id;

CROSS JOIN

The Cartesian product: every row of the left with every row of the right, with no condition. 5 employees × 3 departments = 15 rows.

SELECT COUNT(*) FROM emp CROSS JOIN dept;   -- 15

Useful for generating combinations, such as every size with every color of a product, or every employee with every date of a month. Writing FROM emp, dept with no WHERE also produces a cross join, often by accident.

SELF JOIN

A table joined to itself, using two aliases. The classic use is a hierarchy stored in one table: each employee row points to a manager row.

SELECT e.name AS employee, m.name AS manager
FROM emp e
LEFT JOIN emp m ON e.manager_id = m.emp_id;

employee | manager
---------+--------
Asha     | NULL
Ravi     | Asha
Meera    | Asha
Kiran    | Ravi
Dev      | Meera

A LEFT join keeps Asha, who has no manager. With an inner join she would vanish.

NATURAL JOIN

Joins automatically on all columns with the same name and outputs each shared column once. emp and dept share only dept_id:

SELECT emp_id, name, dept_name FROM emp NATURAL JOIN dept;

emp_id | name  | dept_name
-------+-------+------------
1      | Asha  | Engineering
2      | Ravi  | Engineering
3      | Meera | Sales
5      | Dev   | Sales

It behaves like the inner join here. But if someone later adds a name column to dept, the join condition silently becomes dept_id AND name, and the query returns nothing. Avoid NATURAL JOIN in real code. JOIN dept USING (dept_id) is a safer shorthand that names the join column explicitly.

Joins summary

Finding rows with no match (anti-join)

"Departments with no employees":

SELECT d.dept_name
FROM dept d
LEFT JOIN emp e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

Result: HR. The left join keeps HR with NULLs on the employee side; the WHERE keeps only those. Test a column that cannot be NULL in a real match, such as the primary key.

The ON versus WHERE trap in outer joins

In an inner join, a condition in ON or in WHERE gives the same result. In an outer join it does not.

-- Condition in ON: keeps every department
SELECT d.dept_name, e.name
FROM dept d
LEFT JOIN emp e ON e.dept_id = d.dept_id AND e.salary > 60000;

dept_name   | name
------------+-----
Engineering | Asha
Engineering | Ravi
Sales       | NULL
HR          | NULL

-- Condition in WHERE: turns the left join into an inner join
SELECT d.dept_name, e.name
FROM dept d
LEFT JOIN emp e ON e.dept_id = d.dept_id
WHERE e.salary > 60000;

dept_name   | name
------------+-----
Engineering | Asha
Engineering | Ravi

Why: ON decides which right-side rows match; unmatched left rows survive with NULLs. WHERE runs after the join, and NULL > 60000 is UNKNOWN, so the NULL-padded rows are thrown away. Put conditions on the optional (right) table in ON if you want to keep all left rows.

How joins are executed (preview)

The database chooses among three main algorithms: nested loop join (for each outer row, look up matches, ideally through an index), hash join (build a hash table on the smaller input, probe with the larger), and sort-merge join (sort both inputs on the key and merge). The choice depends on table sizes, indexes and sort order. Details are in query processing and optimization.

Subqueries

A subquery is a query nested inside another. It can appear in WHERE, FROM, SELECT or HAVING.

Scalar subquery

Returns exactly one value (one row, one column) and can be used wherever a value is allowed.

"Employees earning more than the company average":

SELECT name, salary
FROM emp
WHERE salary > (SELECT AVG(salary) FROM emp);

The average is (90000 + 70000 + 60000 + 40000 + 55000) / 5 = 315000 / 5 = 63000. Result: Asha (90000), Ravi (70000).

If a scalar subquery returns more than one row, PostgreSQL, MySQL and SQL Server raise an error; SQLite silently uses the first row. If it returns no rows, the value is NULL.

A scalar subquery in SELECT:

SELECT name,
       (SELECT dept_name FROM dept d WHERE d.dept_id = e.dept_id) AS dept
FROM emp e;

This behaves like a left join (Kiran gets NULL).

Multi-row subquery with IN, ANY, ALL

IN tests membership in the subquery's result. > ANY (subquery) means greater than at least one value; > ALL (subquery) means greater than every value. (SQLite does not support ANY/ALL; PostgreSQL, MySQL, SQL Server and Oracle do. > ALL (...) can be rewritten as > (SELECT MAX(...)), with care if the subquery is empty or has NULLs.)

Correlated subquery

A correlated subquery refers to a column of the outer query, so conceptually it runs once per outer row.

"Employees earning more than the average of their own department":

SELECT name, salary, dept_id
FROM emp e
WHERE salary > (SELECT AVG(salary) FROM emp WHERE dept_id = e.dept_id);

name  | salary | dept_id
------+--------+--------
Asha  | 90000  | 1
Meera | 60000  | 2

Walk through it: for Asha, the inner query averages department 1 = 80000; 90000 > 80000, keep. Ravi: 70000 > 80000 is false. Meera: department 2 averages 57500; 60000 > 57500, keep. Dev: 55000, no. Kiran: dept_id = NULL matches nothing, the average is NULL, and the comparison is UNKNOWN, so she is dropped.

Optimizers usually rewrite correlated subqueries into joins, so "correlated means slow" is not always true, but it is a reasonable first worry on large tables.

Derived table (subquery in FROM)

A subquery in FROM acts as a temporary table and must have an alias.

SELECT dept_id, avg_sal
FROM (SELECT dept_id, AVG(salary) AS avg_sal FROM emp GROUP BY dept_id) AS t
WHERE avg_sal > 60000;

Result: dept 1 with 80000.0. Advanced SQL shows the same idea written as a CTE (WITH), which reads more clearly.

EXISTS versus IN

EXISTS (subquery) is TRUE if the subquery returns at least one row. It does not care what the row contains, so SELECT 1 is conventional.

-- Departments that have at least one employee
SELECT dept_name FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.dept_id);
-- Engineering, Sales

-- Departments that have none: correct
SELECT dept_name FROM dept d
WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.dept_id);
-- HR

-- Departments that have none: WRONG because of Kiran's NULL
SELECT dept_name FROM dept
WHERE dept_id NOT IN (SELECT dept_id FROM emp);
-- (no rows)

On performance: modern optimizers (PostgreSQL, SQL Server, Oracle, MySQL 8) usually turn both IN and EXISTS into the same semi-join plan, so the old advice "EXISTS is always faster" is not reliable. The difference that always matters is correctness with NOT IN and NULLs.

Set operations: UNION, UNION ALL, INTERSECT, EXCEPT

Set operations combine the results of two queries vertically (more rows), whereas joins combine horizontally (more columns). Both queries must return the same number of columns with compatible types. Column names come from the first query.

Cities of Engineering (Bengaluru, Hyderabad) and Sales (Bengaluru, Pune):

SELECT city FROM emp WHERE dept_id = 1
UNION
SELECT city FROM emp WHERE dept_id = 2;
-- Bengaluru, Hyderabad, Pune

SELECT city FROM emp WHERE dept_id = 1
UNION ALL
SELECT city FROM emp WHERE dept_id = 2;
-- Bengaluru, Hyderabad, Bengaluru, Pune

SELECT city FROM emp WHERE dept_id = 1
INTERSECT
SELECT city FROM emp WHERE dept_id = 2;
-- Bengaluru

SELECT city FROM emp WHERE dept_id = 1
EXCEPT
SELECT city FROM emp WHERE dept_id = 2;
-- Hyderabad

UNION ALL is faster than UNION because UNION must sort or hash the combined rows to remove duplicates. Use UNION ALL whenever you know the parts cannot overlap or you want duplicates. MySQL supports INTERSECT and EXCEPT only from 8.0.31. One ORDER BY at the end sorts the combined result.

CASE and COALESCE

CASE is SQL's if-else expression and can be used in SELECT, WHERE, ORDER BY and inside aggregates.

SELECT name, salary,
       CASE
         WHEN salary >= 80000 THEN 'Senior'
         WHEN salary >= 55000 THEN 'Mid'
         ELSE 'Junior'
       END AS band
FROM emp;

Asha is Senior; Ravi, Meera and Dev are Mid; Kiran is Junior. CASE stops at the first true branch, so order the conditions from most to least specific. Without ELSE, unmatched rows get NULL. Advanced SQL uses CASE inside SUM to pivot data.

DELETE versus TRUNCATE versus DROP

All three remove data, at very different levels.

DELETE FROM timesheet WHERE emp_id = 4;   -- some rows
DELETE FROM timesheet;                    -- all rows, table stays
DROP TABLE timesheet;                     -- table and its definition gone

TRUNCATE TABLE timesheet; empties the table quickly in PostgreSQL, MySQL, SQL Server and Oracle. SQLite has no TRUNCATE; a DELETE without WHERE is optimized into a fast truncate internally.

DCL and TCL briefly

-- DCL (PostgreSQL/MySQL syntax; SQLite has no users or GRANT)
GRANT SELECT, INSERT ON emp TO report_writer;
REVOKE INSERT ON emp FROM report_writer;

-- TCL
BEGIN;
UPDATE emp SET salary = salary - 1000 WHERE emp_id = 1;
SAVEPOINT before_bonus;
UPDATE emp SET salary = salary + 1000 WHERE emp_id = 2;
ROLLBACK TO before_bonus;   -- undo only the second update
COMMIT;                     -- keep the first

A transaction is a group of statements that succeed or fail together. COMMIT makes the changes permanent, ROLLBACK undoes them, and a SAVEPOINT lets you roll back part of a transaction. Most client libraries run in autocommit mode by default, where each statement is its own transaction. The guarantees behind this are the subject of transactions and ACID.

Writing a query from a question: a method

When an interviewer gives you a question, work in this order:

1. Which tables hold the data? Name them and the join keys.
2. What is one output row? One per employee? Per department? Per month? This decides GROUP BY.
3. Which rows are excluded before grouping? That is WHERE.
4. Which groups are excluded? That is HAVING.
5. Do unmatched rows need to appear? If yes, LEFT JOIN.
6. NULLs: can any column involved be NULL? Check NOT IN, <>, COUNT(col), AVG.
7. Ties and order: does the question need ORDER BY, and what if values tie?

Example: "For each department, show the name and the number of employees, including departments with none, highest count first."

• One row per department, including HR: start from dept, LEFT JOIN emp.
• Count employees, not rows: COUNT(e.emp_id) gives 0 for HR, while COUNT(*) would give 1 because the NULL-padded row still counts.

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM dept d
LEFT JOIN emp e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY headcount DESC, d.dept_name;

dept_name   | headcount
------------+----------
Engineering | 2
Sales       | 2
HR          | 0

Interview questions

Q1. What is the logical order of execution of a SELECT query?

FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT/OFFSET. This explains why WHERE cannot use aggregates or (in standard SQL) column aliases, while ORDER BY can use aliases. The optimizer may execute physically in a different order as long as the result is the same.

Q2. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot contain aggregate functions. HAVING filters groups after GROUP BY and can use aggregates such as COUNT(*) > 2. Put non-aggregate conditions in WHERE so fewer rows are grouped.

Q3. Why does WHERE col = NULL return nothing?

NULL means unknown, and any comparison with an unknown value is UNKNOWN rather than TRUE. WHERE keeps only rows where the condition is TRUE. Use IS NULL or IS NOT NULL to test for NULL.

Q4. Why can NOT IN return no rows when you expect some?

If the subquery or list contains a NULL, x NOT IN (...) expands to x <> a AND x <> NULL ..., and the NULL comparison is UNKNOWN, so the whole condition can never be TRUE. The query returns no rows. Use NOT EXISTS, or filter NULLs out of the subquery.

Q5. What is the difference between COUNT(*), COUNT(col) and COUNT(DISTINCT col)?

COUNT(*) counts all rows, including those with NULLs. COUNT(col) counts rows where col is not NULL. COUNT(DISTINCT col) counts different non-NULL values. In the sample, these are 5, 4 and 3 for city.

Q6. Explain the different types of joins.

Inner join returns only matching pairs. Left join returns all left rows plus matches, with NULLs where there is none; right join is the mirror; full join returns all rows from both. Cross join returns every combination. A self join joins a table to itself with aliases, for hierarchies like employee and manager. A natural join automatically joins on same-named columns.

Q7. How do you find employees who earn more than their managers?

Self-join the employee table to itself on e.manager_id = m.emp_id and filter WHERE e.salary > m.salary. Return e.name. An inner join is right here because employees without managers cannot satisfy the condition.

Q8. Why does adding a WHERE condition on the right table change a LEFT JOIN's result?

WHERE runs after the join. Rows from the left table without a match have NULLs in the right table's columns, and a condition like e.salary > 60000 is UNKNOWN for them, so they are removed. The left join behaves like an inner join. Put such conditions in the ON clause to keep all left rows.

Q9. What is a correlated subquery?

A subquery that references a column from the outer query, so logically it is evaluated once per outer row. Example: employees earning more than their own department's average, where the inner query uses e.dept_id from the outer row. Optimizers often rewrite them as joins, but on large tables without indexes they can be slow.

Q10. EXISTS or IN: which should you use?

For positive membership tests, modern optimizers usually produce the same plan, so choose for readability. For negative tests, prefer NOT EXISTS, because NOT IN returns nothing if the subquery contains a NULL. EXISTS also expresses "has at least one related row" naturally.

Q11. What is the difference between UNION and UNION ALL?

UNION combines results and removes duplicates, which requires a sort or hash. UNION ALL keeps all rows, including duplicates, and is faster. Use UNION ALL when duplicates are impossible or wanted.

Q12. What is the difference between DELETE, TRUNCATE and DROP?

DELETE is DML: it removes chosen rows one at a time, fires triggers and can be rolled back. TRUNCATE is DDL: it removes all rows quickly by deallocating pages, usually resets identity counters, does not fire row triggers, and is not rollback-able in MySQL or Oracle (it is in PostgreSQL and SQL Server). DROP removes the table itself, including its structure, indexes and constraints.

Q13. Can you use a column alias in WHERE?

Not in standard SQL, because WHERE is evaluated before SELECT creates the alias. Repeat the expression, or wrap the query in a subquery or CTE and filter outside. MySQL and SQLite accept some alias references as an extension, but PostgreSQL and SQL Server do not.

Q14. How do you emulate a FULL OUTER JOIN in MySQL?

Combine a LEFT JOIN and a RIGHT JOIN with UNION. If the data can contain genuine duplicate rows, use UNION ALL and add WHERE left_table.key IS NULL to the right-join half so matched rows are not counted twice.

Q15. What does BETWEEN include?

Both ends: x BETWEEN a AND b means x >= a AND x <= b. With timestamps this is a trap, because BETWEEN '2026-01-01' AND '2026-01-31' excludes times after midnight on the 31st. Use a half-open range: >= '2026-01-01' AND < '2026-02-01'.

Key takeaways

• Clauses are written as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT but evaluated as FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.
• NULL is unknown; comparisons with it are UNKNOWN; use IS NULL, COALESCE, and never NOT IN over a nullable subquery.
• COUNT(*) counts rows; COUNT(col) and other aggregates skip NULLs.
• WHERE filters rows before grouping; HAVING filters groups after.
• Inner, left, right, full, cross, self and natural joins differ in which unmatched rows survive; conditions on the optional side of an outer join belong in ON.
• Use NOT EXISTS or LEFT JOIN ... IS NULL for "has no related row".
• UNION removes duplicates; UNION ALL is faster and keeps them.
• DELETE is row-level DML; TRUNCATE empties fast; DROP removes the table. Rollback behavior depends on the database.

Next lesson

Continue with Advanced SQL.