MySQL interview questions and answers

Database questions come up in nearly every interview for developers, testers and analysts. Interviewers rarely want a recited definition; they want to see that you understand how SQL behaves and can reason about a query. This post collects 30 questions in the order a typical interview moves, from basics to design and performance. The examples were run on MySQL 8.4 using the customers and orders tables from the SQL joins cheat sheet. For a full course, use the free MySQL course.

Basics

1. What is MySQL?

An open-source relational database management system. It stores data in tables of rows and columns, and you query it with SQL (Structured Query Language). It is widely used behind web applications, including WordPress and many PHP sites.

2. What is the difference between SQL and MySQL?

SQL is the language; MySQL is one database system that understands it. Others, such as PostgreSQL and SQL Server, also use SQL with small differences.

3. What are the main types of SQL statements?

• DDL (data definition): CREATE, ALTER, DROP, TRUNCATE.
• DML (data manipulation): INSERT, UPDATE, DELETE, SELECT (the last is often grouped separately as DQL).
• DCL (control): GRANT, REVOKE.
• TCL (transaction): COMMIT, ROLLBACK, SAVEPOINT.

4. What is a primary key? A foreign key?

A primary key uniquely identifies each row; it cannot be NULL and a table has only one. A foreign key is a column that refers to the primary key of another table and enforces that the referenced row exists. Together they link tables, such as orders.customer_id to customers.id.

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

• DELETE removes rows (optionally with WHERE), can be rolled back inside a transaction, and fires triggers.
• TRUNCATE removes all rows quickly and resets AUTO_INCREMENT; it cannot take a WHERE.
• DROP removes the whole table, structure and data.

6. What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed length, VARCHAR(n) stores only the characters used plus a length. Use VARCHAR for most text; CHAR suits fixed-width codes.

Filtering and NULL

7. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation, so it can use aggregate functions.

SELECT status, COUNT(*) AS n FROM orders
WHERE total > 50 GROUP BY status HAVING COUNT(*) >= 2;

8. How does NULL behave?

NULL means unknown. Comparing with it gives NULL, not true or false, so NULL = NULL is not true. Use IS NULL. Tested:

SELECT NULL = NULL AS eq, NULL IS NULL AS isnull, 1 + NULL AS plus, COUNT(customer_id) AS cnt_col, COUNT(*) AS cnt_all FROM orders;

eq	isnull	plus	cnt_col	cnt_all
NULL	1	NULL	4	5

9. What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows (5 above). COUNT(column) counts non-NULL values (4 above). COUNT(DISTINCT column) counts unique non-null values.

10. What does LIKE 'A%' mean? What are % and _?

% matches any number of characters (including none); _ matches exactly one. LIKE 'A%' finds values starting with A; LIKE '_e%' finds values whose second letter is e.

11. IN versus EXISTS?

Both test membership. EXISTS stops at the first match and is often clearer for correlated checks. The big difference is NOT IN versus NOT EXISTS: NOT IN returns nothing if the list contains a NULL.

SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- returns no rows, because order 104 has customer_id NULL
SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- returns Dev

Joins

12. Explain the types of joins.

INNER JOIN returns matching rows only. LEFT JOIN returns all left rows plus matches, with NULLs where there is none. RIGHT JOIN is the mirror image. A full outer join keeps both sides (in MySQL, use UNION of left and right joins). CROSS JOIN returns every combination. A self join joins a table to itself. See the SQL joins cheat sheet.

13. How do you find customers who have no orders?

Left join and test for NULL on the right table's key, or use NOT EXISTS.

SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;

Result: Dev.

14. A left join lost rows after you added a WHERE condition. Why?

A condition on the right table in WHERE rejects the NULL rows that the left join created, turning it into an inner join. Move the condition into ON to keep unmatched left rows.

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

UNION removes duplicate rows (and sorts internally), UNION ALL keeps all rows and is faster. Use UNION ALL when duplicates are impossible or wanted.

Grouping and functions

16. What are aggregate functions?

COUNT, SUM, AVG, MIN, MAX. They collapse many rows into one value, per group when used with GROUP BY.

17. What must be in the GROUP BY clause?

Every selected column that is not inside an aggregate function (MySQL's ONLY_FULL_GROUP_BY mode enforces this by default). Example grouped result from our data: paid has 3 orders totalling 430.50, cancelled 1 and pending 1.

18. What does WITH ROLLUP do?

It adds subtotal and grand-total rows. Grouping orders by status with rollup adds a final row with NULL status and the total count of 5.

19. What is the difference between IFNULL and COALESCE?

IFNULL(a, b) takes two arguments. COALESCE(a, b, c, …) returns the first non-null value of any number of arguments and is standard SQL.

Window functions and subqueries

20. What is a window function?

A function that calculates across related rows without collapsing them, unlike GROUP BY. Examples: ROW_NUMBER(), RANK(), SUM() OVER (…).

21. What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

ROW_NUMBER gives unique numbers. RANK gives ties the same rank and then skips numbers. DENSE_RANK gives ties the same rank without gaps. Tested on our orders ranked by status: three paid rows tie at rank 2 and the next row gets rank 5 with RANK() (it would be 3 with DENSE_RANK()).

22. What is a CTE?

A common table expression, WITH name AS (…), a named temporary result that makes a complex query readable and allows recursion.

23. What is a correlated subquery?

A subquery that refers to a column of the outer query and so runs once per outer row (conceptually). Often a join or window function is faster.

Design, keys and integrity

24. What is normalisation?

Organising tables to reduce duplication. 1NF: each column holds one atomic value, no repeating groups. 2NF: no column depends on only part of a composite key. 3NF: no column depends on another non-key column. Denormalising on purpose can speed up reads, but you take on the work of keeping copies consistent.

25. What does AUTO_INCREMENT guarantee?

Unique, increasing numbers, not gap-free ones. A failed insert, a rollback or an upsert that updates an existing row can use up a value. Tested: after two inserts and an upsert that updated a row, SHOW CREATE TABLE reported AUTO_INCREMENT=4 with only ids 1 and 2 in the table.

26. What are constraints?

Rules enforced by the database: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT. They protect data quality even if application code has bugs.

Performance and transactions

27. What is an index, and when can it hurt?

A sorted structure (a B-tree in InnoDB) that lets MySQL find rows without scanning the whole table. It speeds up WHERE, JOIN and ORDER BY, but costs space and slows INSERT, UPDATE and DELETE. Wrapping an indexed column in a function in WHERE (WHERE YEAR(created) = 2026) can stop MySQL from using it. Check with EXPLAIN: after indexing orders.customer_id, EXPLAIN showed type = ref, key = idx_orders_customer, rows = 2, instead of a full scan (type = ALL).

28. What is a transaction, and what are the ACID properties?

A group of statements that succeed or fail as one. Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions do not see each other's half-finished work) and Durability (committed data survives a crash). InnoDB supports transactions; MyISAM does not. Tested: an UPDATE followed by ROLLBACK left the row unchanged.

29. What is the difference between InnoDB and MyISAM?

InnoDB (the default) supports transactions, foreign keys and row-level locking and recovers after crashes. MyISAM is older, table-locking and has none of those features. Use InnoDB.

Security

30. What is SQL injection and how do you prevent it?

An attacker puts SQL into an input so that it changes the query, for example ' OR '1'='1. Prevent it with prepared statements / parameterised queries, where the query and the values are sent separately, never by gluing user input into the SQL string. Also use accounts with minimal privileges. The PHP course shows it with PDO in CRUD with prepared statements.

How to answer in an interview

• Show a small example. One concrete query beats a paragraph of definitions.
• Mention traps. Knowing about NULL and NOT IN, or the left-join filter, signals real experience.
• Think aloud on query tasks. Say which tables you need, how they join, then what you group and filter.
• Be honest. "I'd check EXPLAIN to see" is better than a guess about performance.

Practice and next steps

Run every example above yourself; reading is not enough. Use the MySQL cheat sheet for syntax, follow the 60-day plan, and test your knowledge with the final exam described in MySQL certificate: how the final exam works.