100% Free
🎯 3200+ Questions. One Goal: Your Success Sign In Sign Up

Mid MySQL Interview Questions

Curated Mid-level MySQL interview questions for developers targeting mid positions. 40 questions available.

Last updated:

MySQL Interview Questions & Answers

Skip to Questions

Welcome to our comprehensive collection of MySQL interview questions and answers. This page contains expertly curated interview questions covering all aspects of MySQL, from fundamental concepts to advanced topics. Whether you're preparing for an entry-level position or a senior role, you'll find questions tailored to your experience level.

Our MySQL interview questions are designed to help you:

  • Understand core concepts and best practices in MySQL
  • Prepare for technical interviews at all experience levels
  • Master both theoretical knowledge and practical application
  • Build confidence for your next MySQL interview

Each question includes detailed answers and explanations to help you understand not just what the answer is, but why it's correct. We cover topics ranging from basic MySQL concepts to advanced scenarios that you might encounter in senior-level interviews.

Use the filters below to find questions by difficulty level (Entry, Junior, Mid, Senior, Expert) or focus specifically on code challenges. Each question is carefully crafted to reflect real-world interview scenarios you'll encounter at top tech companies, startups, and MNCs.

Questions

40 questions
Q1:

What is the difference between INNER JOIN and LEFT JOIN?

Mid

Answer

INNER JOIN returns only matching rows between tables. LEFT JOIN returns all rows from the left table and matching rows from the right, with NULL for non-matching rows.
Quick Summary: INNER JOIN: returns only rows where there is a match in BOTH tables - unmatched rows from either table are excluded. LEFT JOIN (LEFT OUTER JOIN): returns ALL rows from the left table, matched rows from the right table; unmatched right-side columns are NULL. Use LEFT JOIN when the right-side data is optional (e.g., users who may or may not have orders).
Permalink
Q2:

What is a composite primary key?

Mid

Answer

A composite primary key uses multiple columns together to uniquely identify a row, useful when no single column is unique.
Quick Summary: Composite primary key is a primary key made up of two or more columns whose combined values must be unique. Example: PRIMARY KEY (order_id, product_id) for an order_items table. Neither column alone needs to be unique - only the combination. Used for many-to-many junction tables or when no single natural column uniquely identifies a row. InnoDB stores rows in composite PK order.
Permalink
Q3:

What is the use of the DISTINCT keyword?

Mid

Answer

DISTINCT removes duplicate rows from the result set and is used to fetch unique values.
Quick Summary: DISTINCT removes duplicate rows from query results. SELECT DISTINCT country FROM users returns each country only once even if multiple users share the same country. Applied after the query runs - MySQL evaluates all rows then removes duplicates. Can be slow on large result sets. Use GROUP BY as an alternative when also performing aggregation. Cannot DISTINCT on a subset of selected columns.
Permalink
Q4:

What is an alias in MySQL?

Mid

Answer

An alias temporarily renames a table or column to improve readability in complex queries.
Quick Summary: Aliases give a temporary name to a column or table in a query. Column alias: SELECT first_name AS name. Table alias: FROM employees e JOIN departments d ON e.dept_id = d.id. Aliases make queries shorter and more readable. Column aliases can be used in ORDER BY but not in WHERE (evaluated before aliases are applied). Table aliases are required when joining a table to itself (self join).
Permalink
Q5:

What is the purpose of HAVING clause?

Mid

Answer

HAVING filters grouped records after GROUP BY, whereas WHERE filters rows before grouping.
Quick Summary: HAVING filters groups after GROUP BY aggregation - WHERE can't reference aggregate values. SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department HAVING avg_sal > 50000. WHERE runs before grouping (filters individual rows), HAVING runs after grouping (filters groups). Use WHERE to filter rows, HAVING to filter aggregated results.
Permalink
Q6:

What is the difference between UNION and UNION ALL?

Mid

Answer

UNION removes duplicates, UNION ALL returns all rows including duplicates. UNION requires extra processing.
Quick Summary: UNION combines results from two SELECT statements and removes duplicates (deduplication step). UNION ALL combines results and keeps all rows including duplicates (faster - no dedup). Both queries must have the same number of columns with compatible types. Use UNION ALL unless you specifically need duplicate removal - it's always faster since MySQL skips the sort/dedup step.
Permalink
Q7:

What is a subquery?

Mid

Answer

A subquery is a query inside another query used for filtering, comparisons, or dynamic data selection.
Quick Summary: A subquery is a SELECT statement nested inside another SQL statement. Can appear in WHERE (filter with subquery result), FROM (inline view/derived table), SELECT (scalar subquery). Correlated subquery references the outer query's columns and executes once per outer row (can be slow). Non-correlated subquery executes once, result reused. Often replaceable with JOINs for better performance.
Permalink
Q8:

What is a self join?

Mid

Answer

Self join is when a table is joined with itself, useful for hierarchical or relational comparisons.
Quick Summary: A self join joins a table to itself. Requires table aliases to distinguish the two instances. Common use: hierarchical data (employee and their manager in the same table). SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id. The self join treats the same table as two logical tables with different roles.
Permalink
Q9:

What are MySQL data types?

Mid

Answer

MySQL supports numeric, string, date/time, and spatial data types. Choosing correct types improves performance.
Quick Summary: MySQL data types: Numeric (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL), String (CHAR, VARCHAR, TEXT, BLOB, ENUM, SET), Date/Time (DATE, TIME, DATETIME, TIMESTAMP, YEAR), Binary (BINARY, VARBINARY, BLOB types), Spatial types (POINT, GEOMETRY), JSON (native JSON type in MySQL 5.7+). Choose the smallest type that fits your data.
Permalink
Q10:

What is a view?

Mid

Answer

A view is a virtual table based on a SELECT query, storing no physical data but simplifying data access.
Quick Summary: A view is a saved SELECT query presented as a virtual table. Doesn't store data itself - queries the underlying tables each time accessed. Benefits: simplify complex queries (hide JOINs), security (expose only certain columns), abstraction (insulate apps from schema changes). Updatable views allow INSERT/UPDATE/DELETE if they meet certain conditions (no GROUP BY, DISTINCT, UNION, etc.).
Permalink
Q11:

What are the advantages of using views?

Mid

Answer

Views improve security, simplify complex queries, and provide logical abstraction of data.
Quick Summary: View advantages: security (hide sensitive columns, expose only what users need), simplification (encapsulate complex JOINs and filters behind a simple name), consistency (ensure all queries use the same logic), backward compatibility (rename a column but keep old view with old name), and reusability (write the join once, query the view many times).
Permalink
Q12:

What is a stored procedure?

Mid

Answer

A stored procedure is precompiled SQL code stored in the database, reducing repetition and improving performance.
Quick Summary: Stored procedure is a set of SQL statements saved in the database and executed by calling its name. CALL procedure_name(params). Can contain loops, conditionals, error handling. Benefits: reduce network round-trips (logic runs on DB server), reusability, security (grant EXECUTE without exposing tables). Downside: harder to version, debug, and test than application code.
Permalink
Q13:

What is a stored function?

Mid

Answer

A stored function returns a single value and can be used in SQL statements for transformations or calculations.
Quick Summary: Stored function is a routine that takes parameters, performs a computation, and returns a single value. Unlike procedures, functions can be used inside SQL expressions: SELECT get_full_name(first_name, last_name) FROM users. Must return a value. Cannot use CALL - invoked like built-in functions. Useful for computed fields and reusable calculations within queries.
Permalink
Q14:

What is a trigger in MySQL?

Mid

Answer

A trigger executes automatically before or after INSERT, UPDATE, or DELETE, useful for logs or business rules.
Quick Summary: Triggers automatically execute SQL code BEFORE or AFTER INSERT, UPDATE, or DELETE on a table. Use cases: audit logging (record who changed what), maintaining denormalized data, enforcing complex business rules. Each table can have 6 trigger types (BEFORE/AFTER x 3 operations). Triggers can slow write performance - use sparingly. Access OLD (pre-change) and NEW (post-change) row values.
Permalink
Q15:

What is normalization?

Mid

Answer

Normalization organizes data to reduce redundancy and improve integrity by splitting into logical tables.
Quick Summary: Normalization organizes a database to reduce redundancy and improve data integrity. The process involves decomposing tables into smaller, more focused tables and defining relationships between them. This reduces update anomalies (updating one place instead of many), insertion anomalies, and deletion anomalies. Trade-off: more tables = more JOINs, which can impact query performance.
Permalink
Q16:

What are the normal forms?

Mid

Answer

1NF removes repeating groups, 2NF removes partial dependencies, 3NF removes transitive dependencies.
Quick Summary: 1NF: atomic values (no repeating groups or arrays in columns). 2NF: 1NF + no partial dependency (every non-key column depends on the whole primary key). 3NF: 2NF + no transitive dependency (non-key columns depend only on the primary key, not on other non-key columns). BCNF: stricter 3NF. 4NF and 5NF handle multi-valued dependencies. Most production databases target 3NF.
Permalink
Q17:

What is denormalization?

Mid

Answer

Denormalization adds redundancy to improve read performance, often used in analytics and reporting.
Quick Summary: Denormalization intentionally introduces redundancy for performance. Instead of joining tables, store computed/duplicated data in one table. Example: store total_order_amount in the orders table instead of computing it with a SUM JOIN every time. Trade-off: faster reads, slower and more complex writes (must update all copies). Used for read-heavy workloads, analytics, and reporting tables.
Permalink
Q18:

What is a schema in MySQL?

Mid

Answer

A schema is another name for a database, containing tables, views, procedures, and triggers.
Quick Summary: In MySQL, schema and database are synonymous - CREATE SCHEMA and CREATE DATABASE do the same thing. A MySQL server hosts multiple databases/schemas. Each schema contains tables, views, stored procedures, functions, and triggers. Unlike PostgreSQL where schema is a namespace within a database, MySQL uses "database" and "schema" interchangeably as the top-level container.
Permalink
Q19:

What is a transaction?

Mid

Answer

A transaction is a group of SQL operations treated as a single unit that can be committed or rolled back.
Quick Summary: A transaction is a group of SQL operations that execute as a single unit. Either all succeed (COMMIT) or all fail and roll back (ROLLBACK). Start: START TRANSACTION (or BEGIN). End: COMMIT or ROLLBACK. InnoDB supports transactions. MyISAM does not. Use transactions for operations that must be atomic: transferring money (debit + credit must both succeed or both fail).
Permalink
Q20:

What are ACID properties?

Mid

Answer

ACID ensures data reliability: Atomicity, Consistency, Isolation, Durability.
Quick Summary: ACID properties for reliable transactions: Atomicity (all or nothing - transaction either completes fully or not at all), Consistency (data moves from one valid state to another), Isolation (concurrent transactions don't interfere with each other), Durability (committed transactions survive crashes - written to disk/redo log). InnoDB provides full ACID compliance.
Permalink
Q21:

What is a deadlock in MySQL?

Mid

Answer

A deadlock occurs when two transactions wait indefinitely for each other’s locks. MySQL resolves it by rolling back one.
Quick Summary: Deadlock: transaction A holds lock on row 1, waits for row 2. Transaction B holds lock on row 2, waits for row 1. Both wait forever. InnoDB detects deadlocks automatically and rolls back one transaction (usually the one with fewer changes). The application must catch the deadlock error and retry. Prevent by: acquiring locks in consistent order and keeping transactions short.
Permalink
Q22:

What is index cardinality?

Mid

Answer

Cardinality measures uniqueness of indexed values. High cardinality indexes improve search speed.
Quick Summary: Index cardinality is the number of unique values in an indexed column. High cardinality (unique emails, user IDs) = highly selective = index is very useful. Low cardinality (boolean, status with few values) = not selective = MySQL may skip the index and prefer a full table scan. MySQL uses cardinality estimates to decide whether to use an index. Run ANALYZE TABLE to update cardinality stats.
Permalink
Q23:

What is a clustered index?

Mid

Answer

In InnoDB, the primary key creates a clustered index, storing rows physically in key order.
Quick Summary: In InnoDB, the clustered index IS the table - data rows are stored in the B-Tree leaves sorted by the primary key. There is exactly one clustered index per table. All secondary indexes store the primary key value as a reference to find the full row (index lookup then PK lookup). Choosing a good primary key (sequential integers) is critical for InnoDB write performance.
Permalink
Q24:

What is a non-clustered index?

Mid

Answer

A non-clustered index stores pointers to table rows and is used on non-primary key columns.
Quick Summary: In InnoDB, all indexes other than the primary key are non-clustered (secondary indexes). They store the indexed columns plus the primary key value for row lookup. A query using a secondary index often needs two lookups: find the PK in the secondary index, then look up the full row in the clustered index. Covering indexes avoid the second lookup by including all needed columns.
Permalink
Q25:

What is the difference between DELETE and DROP?

Mid

Answer

DELETE removes rows, whereas DROP removes the table structure entirely.
Quick Summary: DELETE removes specific rows but keeps the table structure, can use WHERE, is transactional (rollback possible), fires triggers, logged in binlog. DROP TABLE removes the entire table (structure, data, indexes, triggers) immediately and permanently. DROP is DDL (auto-committed). You can't WHERE on a DROP - it deletes everything. Use DELETE for data removal, DROP to remove the table itself.
Permalink
Q26:

What is ENUM data type?

Mid

Answer

ENUM restricts a column to a predefined list of values, ensuring consistent data input.
Quick Summary: ENUM defines a column that can only store one value from a predefined list. CREATE TABLE t (status ENUM("active", "inactive", "pending")). Stored as an integer internally (1-2 bytes) but displayed as the string. Compared as strings in queries. Advantage: storage efficient, enforces valid values. Downside: adding new values requires ALTER TABLE (which can lock the table).
Permalink
Q27:

What is IN operator?

Mid

Answer

IN checks if a value exists within a list of values and simplifies conditional queries.
Quick Summary: IN checks if a value matches any value in a list. SELECT * FROM users WHERE country IN ("USA", "UK", "Canada"). Equivalent to multiple OR conditions but more readable. Can also use a subquery: WHERE id IN (SELECT user_id FROM orders WHERE total > 100). NOT IN excludes listed values. Caution: NOT IN with a subquery that returns NULL values can give unexpected results.
Permalink
Q28:

What is BETWEEN operator?

Mid

Answer

BETWEEN checks if a value lies within a range, commonly used with dates and numbers.
Quick Summary: BETWEEN checks if a value falls within a range (inclusive of both endpoints). SELECT * FROM orders WHERE amount BETWEEN 100 AND 500 is equivalent to amount >= 100 AND amount <= 500. Works for numbers, dates, and strings. NOT BETWEEN excludes the range. For dates: BETWEEN "2024-01-01" AND "2024-12-31" includes both boundary dates.
Permalink
Q29:

What are aggregate functions?

Mid

Answer

Aggregate functions compute results from sets of rows such as COUNT, SUM, AVG, MIN, and MAX.
Quick Summary: Aggregate functions operate on sets of rows and return a single value. COUNT(*): total rows. COUNT(col): non-NULL values. SUM(col): total. AVG(col): mean. MAX/MIN: extreme values. GROUP_CONCAT(col): strings joined. STDDEV, VARIANCE for statistical analysis. Used in SELECT with or without GROUP BY. Excluding NULLs is default behavior for all except COUNT(*).
Permalink
Q30:

What are scalar functions?

Mid

Answer

Scalar functions operate on individual values such as UPPER, LOWER, CONCAT, and LENGTH.
Quick Summary: Scalar functions return one value per row (unlike aggregate functions that return one value per group). Examples: string functions (UPPER, LOWER, LENGTH, CONCAT, SUBSTRING, TRIM), numeric (ROUND, FLOOR, CEIL, ABS, MOD), date (NOW, DATE_FORMAT, DATEDIFF, YEAR, MONTH, DAY), and control functions (IF, CASE, COALESCE, NULLIF). They transform individual column values.
Permalink
Q31:

What is a join condition?

Mid

Answer

A join condition defines how tables relate, usually through primary and foreign key columns.
Quick Summary: A join condition specifies how tables are related in a JOIN. ON clause: explicit condition (e.g., ON orders.user_id = users.id). USING clause: when the column name is the same in both tables (USING(user_id)). Natural join condition: automatically matches same-named columns (implicit, avoid in production - fragile). Always specify explicit join conditions with ON for clarity and correctness.
Permalink
Q32:

What is a cross join?

Mid

Answer

A cross join returns the Cartesian product of two tables.
Quick Summary: CROSS JOIN returns the Cartesian product - every row from table A combined with every row from table B. 10 rows x 10 rows = 100 result rows. No join condition needed (or specify ON 1=1). Rarely used in practice but useful for: generating combinations, creating a date/time grid, or pairing every item with every other item. Beware: cross joins on large tables create massive result sets.
Permalink
Q33:

What is a natural join?

Mid

Answer

A natural join automatically matches columns with the same name, which can be risky if unintended matches occur.
Quick Summary: NATURAL JOIN automatically joins tables on all columns with the same name. MySQL matches column names implicitly - no ON clause needed. Fragile: adding a same-named column to either table silently changes the join behavior. Not recommended for production code. Use explicit JOIN with ON conditions instead - more readable, predictable, and not affected by future schema changes.
Permalink
Q34:

How does MySQL handle NULL values in comparison?

Mid

Answer

NULL does not equal anything, even another NULL. Use IS NULL or IS NOT NULL for comparisons.
Quick Summary: NULL is not equal to anything, including itself. col = NULL always evaluates to UNKNOWN (not TRUE or FALSE). WHERE col = NULL never matches any rows. Use IS NULL or IS NOT NULL. In ORDER BY, NULLs appear last in ASC, first in DESC. IFNULL(col, "default") and COALESCE(col, "default") provide substitutes. Most aggregate functions ignore NULLs.
Permalink
Q35:

What is a temporary table?

Mid

Answer

A temporary table exists only for the session and is deleted automatically when the session ends.
Quick Summary: Temporary tables exist only for the duration of a session. CREATE TEMPORARY TABLE temp_results (...). Automatically dropped when session closes. Invisible to other connections. Useful for: breaking complex queries into steps, staging intermediate results, reusing a computed result multiple times in a session. Note: "temp table" in EXPLAIN refers to internal query processing temp tables, not user-created ones.
Permalink
Q36:

Difference between COUNT(*) and COUNT(column)?

Mid

Answer

COUNT(*) counts all rows including NULLs, while COUNT(column) counts only non-null values.
Quick Summary: COUNT(*): counts all rows in the result set including NULLs (counts the row, not a column value). COUNT(column): counts rows where the specified column is NOT NULL - skips NULL values. COUNT(DISTINCT col): counts unique non-NULL values. Example: if 10 rows, 2 have NULL email - COUNT(*) returns 10, COUNT(email) returns 8. Use COUNT(*) for total rows, COUNT(col) for non-null counts.
Permalink
Q37:

Join vs Subquery — when to use which?

Mid

Answer

JOINs are faster and used to combine related tables. Subqueries help structure logic but may be slower.
Quick Summary: Use JOIN when: both tables are large (optimizer handles it better), you need rows from both tables in output, the relationship is well-indexed. Use subquery when: you need a result from one table to filter another, readability improves, or using EXISTS for existence checks. Correlated subqueries are generally slow. In most cases, JOINs are faster than equivalent subqueries - prefer JOINs and check with EXPLAIN.
Permalink
Q38:

Difference between DATETIME and TIMESTAMP?

Mid

Answer

DATETIME stores exact values independent of timezone; TIMESTAMP converts values based on timezone and supports auto-update.
Quick Summary: DATETIME: stores date and time without timezone, range 1000-9999. Unchanged on retrieval. TIMESTAMP: stores as UTC internally, converts to server timezone on retrieval. Range: 1970-2038 (32-bit Unix timestamp). TIMESTAMP auto-updates on INSERT/UPDATE if defined with DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. Use TIMESTAMP for "last modified" tracking, DATETIME for future dates or timezone-neutral storage.
Permalink
Q39:

Difference between MyISAM and InnoDB engines?

Mid

Answer

MyISAM is faster for reads but lacks transactions and foreign keys. InnoDB supports row-level locking and ACID transactions.
Quick Summary: MyISAM: older engine, table-level locking (only one writer at a time), no transactions, no foreign keys, faster for simple reads, supports FULLTEXT index in older MySQL. InnoDB: modern engine, row-level locking (many concurrent writers), full ACID transactions, foreign keys, MVCC for concurrent reads and writes, better crash recovery. InnoDB is the default since MySQL 5.5 and should be used for almost all cases.
Permalink
Q40:

What is referential integrity?

Mid

Answer

Referential integrity ensures foreign keys always refer to valid rows, preventing orphaned records.
Quick Summary: Referential integrity ensures relationships between tables stay valid. A foreign key value in the child table must either match a primary key in the parent table or be NULL. InnoDB enforces this: you can't insert a child row with a non-existent parent, and you can't delete a parent row that child rows reference (unless CASCADE is configured). Maintains data consistency across related tables.
Permalink

Curated Sets for MySQL

No curated sets yet. Group questions into collections from the admin panel to feature them here.

People Also Ask - Related MySQL Questions

What is the difference between INNER JOIN and LEFT JOIN?
What is a composite primary key?
What is the use of the DISTINCT keyword?
What is an alias in MySQL?
What is the purpose of HAVING clause?
What is the difference between UNION and UNION ALL?
Ready to level up? Start Practice