Top 30 SQL Interview Questions for 2026: From Joins to Query Optimization

The Comprehensive SQL Checklist for 2026

SQL remains one of the most frequently tested skills across back-end, data engineering, and full-stack roles. Here are the 30 questions you need to know.

Fundamentals & Data Retrieval

• 1. What is the difference between TRUNCATE, DELETE, and DROP? DROP completely removes the table schema and data. TRUNCATE removes all rows quickly without logging individual row deletions. DELETE removes specific rows based on a WHERE clause and logs each deletion.
• 2. Explain the execution order of a SQL query. FROM > JOIN > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT/OFFSET.
• 3. What is the difference between WHERE and HAVING? WHERE filters rows before aggregation; HAVING filters groups after aggregation.
• 4. What are the different types of constraints in SQL? NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.
• 5. What is a Primary Key vs Foreign Key? PK uniquely identifies a record. FK establishes a link between data in two tables.
• 6. Provide an example of a DDL, DML, DCL, and TCL command. DDL (Data Definition): CREATE; DML (Data Manipulation): UPDATE; DCL (Data Control): GRANT; TCL (Transaction Control): COMMIT.
• 7. How do you select unique records from a table? By using the DISTINCT keyword.
• 8. What is the IN operator used for? Checking if a value matches any value in a list or subquery.

Joins & Relationships

• 9. Explain the 4 main types of Joins. INNER JOIN (intersection), LEFT JOIN (all from left table + matches), RIGHT JOIN (all from right + matches), FULL OUTER JOIN (union of left and right).
• 10. What is a Self Join? Joining a table to itself, often used for hierarchical data (e.g., finding an employee's manager in the same employee table).
• 11. What is a Cross Join (Cartesian Product)? A join without a condition, returning the combination of every row in the first table with every row in the second table.
• 12. What is the difference between UNION and UNION ALL? Both combine result sets of two SELECT queries. UNION removes duplicates, UNION ALL keeps duplicates and is faster.
• 13. How do you find records in Table A that are not in Table B? Using a LEFT JOIN and filtering WHERE TableB.ID IS NULL, or using the EXCEPT/MINUS operator.
• 14. Can you JOIN a table with itself without using a primary key? Yes, you can use any column that allows you to establish the required relationship, though it may result in a many-to-many Cartesian expansion.

Aggregation & Functions

• 15. How does GROUP BY work? It groups rows that have the same values into summary rows, usually used with aggregate functions like COUNT(), MAX(), SUM().
• 16. What is the difference between COUNT(column_name) and COUNT(*)? COUNT(*) counts all rows including NULLs. COUNT(column_name) ignores NULLs.
• 17. What are string manipulation functions you frequently use? CONCAT(), SUBSTRING(), LENGTH()/LEN(), UPPER(), LOWER(), TRIM().
• 18. How do you handle NULL values in equations? Using COALESCE(), ISNULL(), or NVL() to replace NULL with a default value.
• 19. What does the NULLIF() function do? Compares two expressions and returns NULL if they are equal; otherwise, it returns the first expression.

Advanced SQL: Window Functions & CTEs

• 20. What is a Window Function? A function that performs a calculation across a set of table rows that are somehow related to the current row, without collapsing the result set (unlike GROUP BY).
• 21. Difference between ROW_NUMBER(), RANK(), and DENSE_RANK()? ROW_NUMBER assigns sequential integers (1,2,3). RANK handles ties by leaving a gap (1,2,2,4). DENSE_RANK handles ties without gaps (1,2,2,3).
• 22. What is a CTE (Common Table Expression)? A temporary named result set defined within the execution scope of a single statement. Created using the WITH clause.
• 23. How do you write a recursive CTE? A CTE that references itself, typically containing a base case (anchor member) and a recursive step combined using UNION ALL. Often used for parsing trees.
• 24. Explain the LEAD() and LAG() functions. Window functions to access data from the subsequent row (LEAD) or previous row (LAG) without the use of a self-join.

Database Architecture & Optimization

• 25. What is Normalization vs Denormalization? Normalization reduces redundancy by dividing large tables into smaller ones (1NF, 2NF, 3NF). Denormalization intentionally adds redundancy to speed up complex read queries.
• 26. What is an Index? Clustered vs Non-Clustered? Indexes speed up data retrieval. Clustered indexes determine the physical order of data in the table (1 per table). Non-clustered indexes are stored separately referencing the data row (multiple allowed).
• 27. What is an Execution Plan / Query Plan? A roadmap generated by the database engine showing how it will execute a query. Used to identify bottlenecks like full table scans.
• 28. What are ACID properties? Atomicity (all or nothing), Consistency (valid states), Isolation (concurrent transactions don't interfere), Durability (committed data is permanent).
• 29. How do you prevent SQL Injection? By using prepared statements, parameterized queries, and stored procedures.
• 30. Name 3 ways to optimize a slow SQL Query. 1) Add appropriate indexes. 2) Avoid SELECT *; select only necessary columns. 3) Replace correlated subqueries with JOINs or CTEs.

Legal Disclaimer

MockExperts provides educational content for interview preparation. Company names and database brands mentioned are for context only. No endorsement is implied.