I keep a folder of MySQL interview prep that grows about as fast as the engine itself. Every time someone asks me what to study for a database interview, the honest answer is the same: the basics have changed more in the last three years than they did in the ten before that. If your study notes still mention mysql_pconnect (a persistent connection function removed in PHP 7.0 and irrelevant to MySQL itself for years before that) or quote the old string-comparison rules, an experienced interviewer will know in about ten seconds. The five questions below are the ones I have watched people stumble on most often in the last year, and the version of MySQL we are talking about is 9.7 LTS or 8.4 LTS, both currently under Premier Support (Oracle’s paid maintenance tier with security patches and bug fixes).
What interviewers are actually testing when they ask about version
The first question in almost every modern MySQL interview is some variant of “which version would you deploy today, and why?” It looks like a softball, but the right answer is not “the latest.” MySQL now has two release tracks. The LTS track (8.4 and 9.7) gets five years of Premier Support, then extended support on a paid basis. The Innovation track (the 8.x and 9.x non-LTS releases) gets bug fixes for about six months and then you are expected to upgrade. If your interviewer cares about operations, they want to hear you can articulate that split and pick an LTS for production. If they care about new features, they want to know you have looked at the innovation track release notes. Picking either is fine; not knowing there is a difference is a red flag.
The follow-up is usually about getting the actual version out of the server. The query is short enough that interviewers expect you to type it from memory:
mysql> SELECT VERSION(), DATABASE();
+-----------+------------+
| VERSION() | DATABASE() |
+-----------+------------+
| 9.7.2 | NULL |
+-----------+------------+
1 row in set (0.00 sec)
The NULL in the second column is the part most candidates miss. VERSION() returns the server build. DATABASE() returns whatever schema your session has selected, which is nothing until you run USE tecmint (or whatever your schema is). The reason this matters in interviews: candidates who cannot explain the NULL are usually the ones who copy-pasted their session setup from a Stack Overflow answer and never tested it on a fresh server.
The NOT-operator question that quietly tests null handling
The second most common stumble is a deceptively simple filtering question. “Show me every user except Sam.” Most candidates write:
mysql> SELECT * FROM users WHERE name != 'Sam';
That works on a non-null dataset. It does not work the way most people expect when the name column can be NULL. The standard NULL semantics in SQL (the three-valued logic where NULL is treated as “unknown,” not “false”) mean a row with name = NULL is excluded from the result, even though it is also not Sam. If your interviewer has a NULL row in their test fixture, your answer is wrong in a way that is hard to spot unless you actually run the query.
The fix is either WHERE name != 'Sam' OR name IS NULL if you want to include unknown values, or just WHERE NOT (name = 'Sam') and accept that NULL rows drop out. The interview trap is whether you noticed the column could be NULL in the first place. If the answer is “I would check the schema before answering,” you are already ahead of most candidates.
A second related question that almost always comes up is whether NOT can be combined with AND. Yes, but it requires parentheses, and the natural-language reading of NOT a AND b does not match the operator precedence (the order the SQL parser applies the operators). NOT a AND b parses as (NOT a) AND b, which means “neither a nor b must be false.” That is rarely what people meant. The right shape is NOT (a AND b), which is “it is not the case that both a and b.” The De Morgan’s laws translation (the rule that distributes NOT across AND/OR: NOT(a AND b) becomes NOT a OR NOT b) usually surfaces about three questions later.
NULL handling, IFNULL, and the COALESCE trap
The third question cluster is around NULL handling, and the version that trips people up is the difference between IFNULL and COALESCE. IFNULL takes two arguments and returns the first if it is not NULL, otherwise the second. COALESCE takes a list and returns the first non-NULL argument. The interview trap: someone will ask which one you would use for a contact column where email might be NULL and you want to fall back to city. COALESCE is the right answer because it generalizes to N columns without rewriting the query. IFNULL is shorter when you only have two cases, but it does not compose.
NULLIF is the third function in this family and the one most candidates have never used. NULLIF(a, b) returns NULL if a equals b, otherwise it returns a. The classic use case is divide-by-zero protection:
mysql> SELECT amount / NULLIF(quantity, 0) FROM orders;
NULLIF returns NULL when quantity is 0, and dividing by NULL is NULL, which is almost always what you want. If you write the query without NULLIF, MySQL raises an error on the first zero quantity and the whole SELECT fails. That is the kind of one-line fix that separates a senior candidate from a junior one, and it is the function people are most likely to never have written themselves.
A bonus point if you can explain why MySQL chose this design instead of just returning 0 or NULL when the divisor is 0: it is because SQL NULL semantics say the result of an arithmetic operation involving NULL is itself NULL, which preserves the invariant that NULL propagates through expressions the way unknown propagates through real math.
LIMIT without ORDER BY, and the pagination question
The fourth question cluster is about LIMIT, and the version I have watched fail most often is the “show me the most recent N users” follow-up. The naive answer is SELECT * FROM users LIMIT 5. The correct call is SELECT * FROM users ORDER BY joined DESC LIMIT 5. The reason: LIMIT without ORDER BY is allowed, but the order is implementation-defined, which means MySQL is free to return rows in any order it wants (and on parallel scans it often does). If the candidate does not know that, they will get the rows in some order and not realize they are wrong.
The pagination follow-up is the harder part. The natural answer to “show page 3 of 20 results” is:
mysql> SELECT id, name FROM users ORDER BY id LIMIT 20 OFFSET 40;
That works. It is also slow on large tables. The reason: MySQL has to scan and discard the first 40 rows before returning the next 20, which gets linearly worse as the offset grows. For an interview at a company with tables in the millions, the correct call is keyset pagination (also called seek pagination, a method where you remember the last value from the previous page and use a WHERE clause to start from there instead of skipping rows):
mysql> SELECT id, name FROM users WHERE id > 40 ORDER BY id LIMIT 20;
The candidate who can articulate both the obvious answer and the scale-dependent answer is the one who has actually written this on a real database, not just on a tutorial fixture. That is the line between “knows MySQL” and “has shipped a system on MySQL.”
What I would tell past me about MySQL interview prep
If I could go back three years and redo my own MySQL interview prep, four things would be higher on the list:
- Run the queries against a fresh server. The fastest way to internalize NULL semantics and LIMIT ordering is to set up an empty MySQL 9.7 instance, create a
userstable with one NULL row and one duplicate, and actually run the interview questions against it. Reading the answers in a book is not the same as typing them. - Read the release notes for at least one Innovation release. The interview question “what is new in MySQL 9.x” is not a trick question. They want to know if you have looked. The JSON table functions, the new EXPLAIN format, and the JavaScript stored programs are all in the 9.x release notes and are the kind of thing a senior candidate mentions off the cuff.
- Practice the pagination question on a table with 100k rows. The keyset-vs-OFFSET distinction is invisible on a 5-row sample. It is obvious on a 100k-row sample because the OFFSET version is several seconds slower and the keyset version is identical in time.
- Time yourself on each question. Most candidates rehearse at their desk and never simulate interview pressure. Set a timer for five minutes per question, type the answer from memory, and run it against the fresh server. The questions you cannot answer in five minutes are the ones to focus on, not the ones you can already do.
Trade-offs
MySQL interview prep is not free in study time. The five questions above cover roughly a third of what an experienced interviewer might ask, and a serious candidate should plan on 20-40 hours of practice to cover the rest. The cost is real for people who are already working full-time and interviewing on weekends.
Interview prep is also not free in version drift. MySQL has changed enough between 5.7 and 9.7 that answers from older books are actively misleading. The cost of using stale prep material is real, and the only defense is to test every example against a current LTS.
Interview prep is not free in scope creep, either. It is tempting to also study PostgreSQL, Redis, and the general database landscape, and at some point that becomes the bottleneck. The most efficient prep is to spend 80 percent of study time on the version you are interviewing for and reserve the rest for breadth.
For someone targeting a role that lists MySQL 8 or 9 specifically, this is a clear win: focus on the LTS release notes, run the queries on a fresh instance, and rehearse the NULL and pagination questions until the answers come out without thinking. For someone interviewing more broadly, MySQL is one of three or four databases they should know, and the right move is to spend less time on each one and more time on the cross-cutting fundamentals (transactions, indexes, query planning).
Bottom line
If you have a database interview in the next two weeks, the highest-impact move is to set up an empty MySQL 9.7 instance today and type through the five questions above. None of them take more than five minutes per question to rehearse, and the difference between “I have read about this” and “I can run this on a fresh server” is the difference between passing the phone screen and getting bounced at the technical loop. The questions are not exotic. They are the questions that separate a candidate who has read MySQL from a candidate who has worked on MySQL, and they are the ones your interviewer is going to ask first.