MySQL Interview – Fundamentals

The questions almost every SQL interview asks, answered the way you would say them out loud. Each one links to the lesson that covers it properly.

What are the join types?

INNER keeps rows matching on both sides. LEFT keeps every row from the left table, filling the right with NULLs where there is no match. RIGHT is the same the other way round. CROSS pairs every row with every row and takes no ON. MySQL has no FULL OUTER JOIN — you write a UNION of a left and a right join.

The good follow-up answer: a left join is what you use when the report must have one row per order whether or not the customer has an account, and the mistake to avoid is putting a condition on the right-hand table in WHERE — that turns it back into an inner join, silently. LEFT JOIN

WHERE versus HAVING

WHERE filters rows before grouping and cannot see aggregates. HAVING filters groups after and can. When a condition could go in either — one on a plain column — put it in WHERE, because filtering before grouping means less work and lets an index help. GROUP BY and HAVING

DELETE, TRUNCATE and DROP

DELETE

What is an index, and what does it cost?

A B-tree holding a column's values in sorted order with pointers to the rows, so a lookup is a few steps down a tree instead of a scan of every row. On a 400,000-row table that is the difference between examining 396,091 rows and examining 8.

It costs on every write — each insert, update and delete has to maintain it — plus disk and memory. So index what you filter, join and sort on, and not "just in case". The follow-up worth volunteering: a function around the column in WHERE makes the index unusable, which is why WHERE YEAR(created_at) = 2024 is slow and a half-open range is not. Indexes

Primary key versus unique key

One primary key per table; it is implicitly NOT NULL and, in InnoDB, it is the clustered index — the rows are physically stored in its order. Several unique keys are allowed, and they can contain NULLs, more than one of them, because NULLs are not equal to each other. CREATE TABLE

Why does = NULL return nothing?

NULL means unknown, so any comparison with it evaluates to unknown rather than true or false — and WHERE keeps only rows that are true. Use IS NULL.

Two follow-ups that separate candidates. COUNT(*) counts rows while COUNT(col) counts non-NULL values. And NOT IN against a subquery containing a single NULL returns no rows at all — which is why NOT EXISTS is the safer habit. NULL and IS NULL

What does ACID mean?

• Atomicity — all statements in the transaction take effect or none do. Saving an order and its line items must not half-succeed.
• Consistency — constraints hold at the end: foreign keys, unique keys, checks.
• Isolation — concurrent transactions do not see each other's partial work. How much they see is the isolation level.
• Durability — once committed, it survives a crash, via InnoDB's redo log.

Strong answer to add: MySQL defaults to REPEATABLE READ where most databases default to READ COMMITTED, and the gap locks that make it work are a common source of deadlocks. Transactions

What is normalization?

Organising tables so each fact is stored exactly once.

• 1NF — one value per cell. No item_1, item_2 columns, no comma-separated lists.
• 2NF — no column depending on only part of a composite key.
• 3NF — no column depending on another non-key column. An order should hold user_id, not a copy of the user's email.

The informal version: every non-key column depends on the key, the whole key, and nothing but the key. Denormalize deliberately and say why — an order_item that snapshots the price paid is not a normalization failure, because "what this customer was charged" is a fact about the order. Normalization

UNION versus UNION ALL

UNION removes duplicate rows; UNION ALL keeps everything. Deduplication costs a sort or hash over both branches, so default to UNION ALL and use plain UNION only when duplicates are both possible and unwanted — it can otherwise silently delete real rows that happen to match. UNION

What is a transaction, and how do you use one?

START TRANSACTION, then COMMIT or ROLLBACK. Autocommit is on by default, so every statement is otherwise its own transaction.

The MySQL-specific point worth knowing: DDL causes an implicit commit. A CREATE TABLE in the middle of your transaction commits everything before it, and a later ROLLBACK does nothing. Transactions

How do you find out why a query is slow?

EXPLAIN it. Read type first — ALL on a large table with a selective WHERE is the thing to chase — then look for the step examining far more rows than it returns. Using index is good (covered); Using filesort with Using temporary is worth investigating.

Say that rows is an estimate from sampled statistics, that it drifts between identical runs, and that EXPLAIN ANALYZE gives real counts because it actually executes. EXPLAIN

What is a deadlock?

Two transactions each holding a lock the other needs. InnoDB detects the cycle immediately and rolls one of them back with error 1213.

The answer they are listening for: this is normal under concurrency, so the application must retry — and because the whole transaction was rolled back, it must retry the whole thing. Prevent them with consistent lock ordering. Distinguish it from a lock wait timeout (1205), which is one transaction holding a lock too long and rolls back only the statement. Deadlocks

DECIMAL or FLOAT for money?

DECIMAL, always. Binary floating point cannot represent 0.1 exactly, so errors accumulate: 0.1 + 0.2 as FLOAT gives 0.30000000447034836. DECIMAL(10,2) gives 0.30. ROUND even behaves differently between the two. Data types

How do you prevent SQL injection?

Parameterised queries — a PreparedStatement with ? placeholders. The value travels separately from the SQL, so it can never be parsed as SQL. Not escaping, not a blocklist, not validation: those are defence in depth, and the placeholder is the actual fix.

Add least privilege: the application account should hold SELECT, INSERT, UPDATE, DELETE and not DROP, so an injection that gets through cannot destroy the schema. Users and privileges

What if you do not know?

Say so, then say how you would find out — EXPLAIN, the documentation, a test against a copy. Interviewers are calibrating how you work as much as what you remember, and a confident wrong answer about isolation levels reads worse than "I would check".

Harder query-writing questions are in advanced queries.