10
Most Asked
SQL Questions
with Answers
Nitin Sahu
Difference between
1 INNER JOIN and OUTER
JOIN
INNER JOIN combines rows
from two tables where the
join condition is met. OUTER
JOIN returns all rows from
one table and matching
rows from the other table, if
any.
Nitin Sahu
2 Finding duplicate records
Duplicate records can be
found using the GROUP BY
clause with the HAVING
COUNT(*) > 1 condition.
This returns groups of rows
with duplicate values.
Nitin Sahu
3 Window functions
Window functions, such as
ROW_NUMBER(), RANK(), and
LAG(), perform calculations
across rows that are related
to the current row. They are
useful for tasks like data
ranking, aggregation, and
time-series analysis.
Nitin Sahu
Second-highest salary
4 query
The second-highest salary can
be found using the following
query:
SELECT MAX(salary) AS
second_highest_salary
FROM employees
WHERE salary < (SELECT
MAX(salary) FROM employees);
Nitin Sahu
5 WHERE vs. HAVING clauses
The WHERE clause filters rows
based on conditions applied to
individual rows. The HAVING
clause filters groups of rows
based on conditions applied to
the grouped results.
Nitin Sahu
6 UNION vs. UNION AL
UNION returns only unique
rows from the combined result
sets, removing duplicates.
UNION ALL returns all rows,
including duplicates.
Nitin Sahu
Optimizing slow SQL
7 queries
Slow queries can be optimized by:
- Analyzing the query execution plan
- Indexing columns used in WHERE and JOIN
clauses
- Optimizing JOIN order and using efficient
join types
- Limiting result sets using pagination or
filtering
- Avoiding SELECT *
- Regularly updating statistics and rebuilding
indexes
Nitin Sahu
Common Table
8 Expressions (CTEs)
CTEs are temporary result sets
that can be referenced within a
SELECT, INSERT, UPDATE, or DELETE
statement. They simplify
complex queries, reduce code
duplication, and improve
readability.
Nitin Sahu
9 Handling NULL values
NULL values can be handled using:
- IS NULL and IS NOT NULL conditions
- COALESCE() function to replace
NULL values with defaults
- IFNULL() function to replace NULL
values with defaults
- NULLIF() function to return NULL if
two values are equal
Nitin Sahu
Clustered vs. non-clustered
10
indexes
Clustered indexes reorder the
physical rows of the table
according to the index keys,
improving query performance.
Non-clustered indexes create a
separate data structure
containing the index keys and
row pointers, reducing storage
requirements.
Nitin Sahu