50+ SQL Joins Interview Questions & Answers | INNER, LEFT, RIGHT, FULL Joins

Q: What are SQL Joins, and why are they used?

SQL Joins are used to combine data from multiple tables based on a related column. Joins help retrieve meaningful insights from relational databases. For example, in an Indian retail database: Customers table (CustomerID, Name, City) Orders table (OrderID, CustomerID, Amount) A JOIN can combine customer details with their order history.

Q: What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN : Returns matching rows from both tables. OUTER JOIN : Returns matching and non-matching rows, filling gaps with NULLs. -- INNER JOIN (Only matching customers with orders) SELECT Customers.Name, Orders.Amount FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Q: Explain LEFT JOIN with an example.

Returns all rows from the left table , even if there's no match in the right table. -- Show all customers, even those who haven?t placed orders SELECT Customers.Name, Orders.Amount FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Q: Explain RIGHT JOIN with an example.

Returns all rows from the right table , even if there's no match in the left table. -- Show all orders, even if some customers are missing SELECT Customers.Name, Orders.Amount FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Q: What is FULL OUTER JOIN? How does it work?

Returns all records from both tables , filling gaps with NULLs. SELECT Customers.Name, Orders.Amount FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Q: What is CROSS JOIN in SQL? When should it be used?

Produces a cartesian product , multiplying each row from table A with each row from table B. SELECT Employees.Name, Departments.DepartmentName FROM Employees CROSS JOIN Departments; Used in scenarios like generating price combinations or test data .

Q: What is a SELF JOIN, and where is it used?

A SELF JOIN joins a table to itself. Example: Find Indian employees and their managers from the same table. SELECT E1.Name AS Employee, E2.Name AS Manager FROM Employees E1 JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;

Q: What is the difference between JOIN and UNION?

JOIN merges columns from two tables. UNION merges rows from two queries. -- UNION Example (Combine lists of Mumbai & Delhi customers) SELECT Name FROM Customers WHERE City = 'Mumbai' UNION SELECT Name FROM Customers WHERE City = 'Delhi';

Q: What happens when you use JOIN without a condition?

It creates a CROSS JOIN , generating all possible combinations of rows.

Q: Can you perform a JOIN on more than two tables?

Yes, you can join multiple tables . SELECT Customers.Name, Orders.Amount, Payments.PaymentDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID JOIN Payments ON Orders.OrderID = Payments.OrderID;

medium

sql

What are SQL Joins, and why are they used?

SQL Joins are used to combine data from multiple tables based on a related column. Joins help retrieve meaningful insights from relational databases.

For example, in an Indian retail database:

Customers table (CustomerID, Name, City)
Orders table (OrderID, CustomerID, Amount)

A JOIN can combine customer details with their order history.

medium

sql

What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN: Returns matching rows from both tables.
OUTER JOIN: Returns matching and non-matching rows, filling gaps with NULLs.

-- INNER JOIN (Only matching customers with orders)
SELECT Customers.Name, Orders.Amount
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

medium

sql

Explain LEFT JOIN with an example.

Returns all rows from the left table, even if there's no match in the right table.

-- Show all customers, even those who haven?t placed orders
SELECT Customers.Name, Orders.Amount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

medium

sql

Explain RIGHT JOIN with an example.

Returns all rows from the right table, even if there's no match in the left table.

-- Show all orders, even if some customers are missing
SELECT Customers.Name, Orders.Amount
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

medium

sql

What is FULL OUTER JOIN? How does it work?

Returns all records from both tables, filling gaps with NULLs.

SELECT Customers.Name, Orders.Amount
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

medium

sql

operating-systems

What is CROSS JOIN in SQL? When should it be used?

Produces a cartesian product, multiplying each row from table A with each row from table B.

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;

Used in scenarios like generating price combinations or test data.

medium

sql

What is a SELF JOIN, and where is it used?

A SELF JOIN joins a table to itself.

Example: Find Indian employees and their managers from the same table.

SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;

medium

sql

What is the difference between JOIN and UNION?

JOIN merges columns from two tables.
UNION merges rows from two queries.

-- UNION Example (Combine lists of Mumbai & Delhi customers)
SELECT Name FROM Customers WHERE City = 'Mumbai'
UNION
SELECT Name FROM Customers WHERE City = 'Delhi';

medium

sql

What happens when you use JOIN without a condition?

It creates a CROSS JOIN, generating all possible combinations of rows.

medium

sql

Can you perform a JOIN on more than two tables?

Yes, you can join multiple tables.

SELECT Customers.Name, Orders.Amount, Payments.PaymentDate
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN Payments ON Orders.OrderID = Payments.OrderID;

medium

sql

What is the difference between LEFT JOIN and RIGHT JOIN?

LEFT JOIN - All rows from the left table + matches from the right table.
RIGHT JOIN - All rows from the right table + matches from the left table.

medium

sql

operating-systems

What is the difference between CROSS JOIN and INNER JOIN?

CROSS JOIN - Produces all possible combinations.
INNER JOIN - Returns only matching rows.

medium

sql

How does a FULL OUTER JOIN handle NULL values?

It fills missing values with NULL where no match is found.

medium

sql

What are EQUI JOIN and NON-EQUI JOIN?

EQUI JOIN - Uses = operator (Example: ON A.ID = B.ID)
NON-EQUI JOIN - Uses <, >, <=, etc.

SELECT Employees.Name, Salaries.Amount
FROM Employees
JOIN Salaries ON Employees.Salary > Salaries.MinSalary;

medium

sql

How do you filter data in a JOIN using WHERE vs. ON?

ON - Filters data before joining.
WHERE - Filters data after joining.

SELECT * FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.City = 'Delhi';

medium

sql

What is the difference between NATURAL JOIN and INNER JOIN?

NATURAL JOIN automatically finds common columns.
INNER JOIN requires explicit ON conditions.

SELECT * FROM Customers NATURAL JOIN Orders;

medium

sql

Can you use GROUP BY with SQL Joins? Provide an example.

Yes, it?s used for aggregations.

SELECT City, COUNT(Orders.OrderID)
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY City;

medium

sql

How do you use ORDER BY with SQL Joins?

SELECT Customers.Name, Orders.Amount
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Orders.Amount DESC;

medium

sql

How do you use HAVING with SQL Joins?

Used for filtering aggregated data.

SELECT City, COUNT(OrderID) AS OrderCount
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY City
HAVING COUNT(OrderID) > 10;

medium

sql

How do INDEXES affect performance in SQL Joins?

Indexes improve JOIN performance by making lookups faster.
Clustered Indexes store data in sorted order.
Non-clustered Indexes store pointers to data.

CREATE INDEX idx_CustomerID ON Orders(CustomerID);

medium

sql

operating-systems

What is a Composite Key Join?

A join using multiple columns.

SELECT * FROM Orders
JOIN Payments ON Orders.OrderID = Payments.OrderID
AND Orders.CustomerID = Payments.CustomerID;

medium

sql

What are Self-Referencing Joins, and how do they work?

Used for hierarchical data.

SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;

medium

sql

networking

What are the performance implications of using multiple joins?

Using multiple joins can slow down performance if:

Indexes are missing on join columns.
Too many tables are joined at once.
Joins involve large datasets without filters.

Optimization Tip: Use EXPLAIN PLAN in SQL Server or MySQL to check query execution.

EXPLAIN SELECT * FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN Payments ON Orders.OrderID = Payments.OrderID;

medium

sql

How can you improve JOIN performance in large datasets?

Use Indexes on join columns.
Filter data early using WHERE or ON.
Use proper join order (smaller datasets first).
**Avoid SELECT *** (fetch only needed columns).
Consider partitioning for huge tables.

Example: Using an index on CustomerID for faster lookups

CREATE INDEX idx_customerID ON Orders(CustomerID);

medium

sql

What is an ANTI JOIN, and how do you implement it?

An ANTI JOIN finds rows in one table that do not have a match in another.

Example: Find customers who haven?t placed any orders

SELECT Customers.Name
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.CustomerID IS NULL;

medium

sql

How can you replace FULL OUTER JOIN using LEFT JOIN and RIGHT JOIN?

Since FULL OUTER JOIN is not supported in MySQL, it can be replaced with UNION.

SELECT Customers.Name, Orders.Amount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

UNION

SELECT Customers.Name, Orders.Amount
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

medium

sql

java

data-structures

git

Explain the difference between MERGE JOIN, HASH JOIN, and NESTED LOOP JOIN.

These are SQL Server execution plans:

Nested Loop Join: Best for small datasets.
Hash Join: Used for large, unsorted data.
Merge Join: Used when both tables are sorted.

SELECT * FROM Employees
JOIN Salaries ON Employees.Salary = Salaries.Amount
OPTION (MERGE JOIN);

medium

sql

How do you use a JOIN inside a subquery?

Example: Get all Delhi customers who placed an order above Rs. 5000

SELECT Name FROM Customers
WHERE CustomerID IN (
    SELECT CustomerID FROM Orders WHERE Amount > 5000
);

hard

sql

What is a Recursive Join, and how is it implemented in SQL?

Used for hierarchical data (e.g., company hierarchy).

Example: Find an employee's chain of managers

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, Name, ManagerID
    FROM Employees
    WHERE EmployeeID = 101  -- Start with an Employee
    UNION ALL
    SELECT E.EmployeeID, E.Name, E.ManagerID
    FROM Employees E
    JOIN EmployeeHierarchy EH ON E.ManagerID = EH.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

Database-Specific Behavior

PostgreSQL / MySQL 8+ - WITH RECURSIVE is required.
SQL Server / Oracle - WITH alone is sufficient (recursive behavior is inferred).
SQLite - WITH RECURSIVE is required.

hard

sql

How do window functions work with joins?

Window functions like RANK() or DENSE_RANK() work with joins for ranking within groups.

Example: Find top 3 highest salaries per department

SELECT E.Name, E.Department, S.Amount,
       RANK() OVER (PARTITION BY E.Department ORDER BY S.Amount DESC) AS Rank
FROM Employees E
JOIN Salaries S ON E.EmployeeID = S.EmployeeID
WHERE Rank <= 3;

easy

general

How do you find employees who don?t have a manager?

SELECT Name FROM Employees WHERE ManagerID IS NULL;

easy

general

How do you retrieve customers who haven?t placed any orders?

SELECT Customers.Name FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.CustomerID IS NULL;

easy

general

How do you fetch the latest order for each customer?

SELECT O.*
FROM Orders O
JOIN (
    SELECT CustomerID, MAX(OrderDate) AS LatestOrder
    FROM Orders
    GROUP BY CustomerID
) Latest ON O.CustomerID = Latest.CustomerID AND O.OrderDate = Latest.LatestOrder;

easy

general

How do you find departments that have no employees?

SELECT Departments.DepartmentName
FROM Departments
LEFT JOIN Employees ON Departments.DepartmentID = Employees.DepartmentID
WHERE Employees.EmployeeID IS NULL;

medium

sql

How do you use joins to detect duplicate records?

SELECT Name, COUNT(*)
FROM Customers
GROUP BY Name
HAVING COUNT(*) > 1;

easy

general

How do you find the second-highest salary in each department?

SELECT Name, Department, Salary
FROM (
    SELECT Name, Department, Salary,
           DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rnk
    FROM Employees
) Ranked
WHERE rnk = 2;

medium

sql

git

How do you use joins to merge data from two different databases?

SELECT A.Name, B.Amount
FROM db1.Customers A
JOIN db2.Orders B ON A.CustomerID = B.CustomerID;

medium

sql

What is the best way to join large tables without performance issues?

Use indexes
Use partitioning
Filter data before joining
Use EXISTS instead of IN()

medium

sql

How can you implement pagination using joins?

Pagination is useful for displaying large datasets page by page.

Example: Fetching page 2 with 10 records per page

SELECT Customers.Name, Orders.Amount
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Orders.OrderDate DESC
LIMIT 10 OFFSET 10;

LIMIT 10 - Fetch 10 records
OFFSET 10 - Skip the first 10

medium

sql

How do you use joins to compare historical vs. current data?

Used to track changes in employee salaries.

SELECT E1.EmployeeID, E1.Name, E1.Salary AS CurrentSalary, E2.Salary AS PreviousSalary
FROM Employees E1
LEFT JOIN EmployeeHistory E2 ON E1.EmployeeID = E2.EmployeeID
WHERE E1.Salary <> E2.Salary;

This compares current salaries (Employees table) vs. past salaries (EmployeeHistory table).

medium

sql

How do derived tables work with joins?

A derived table is a subquery used inside a JOIN.

Example: Fetch customers who have spent more than Rs.10,000

SELECT C.Name, Spending.TotalAmount
FROM Customers C
JOIN (
    SELECT CustomerID, SUM(Amount) AS TotalAmount
    FROM Orders
    GROUP BY CustomerID
    HAVING SUM(Amount) > 10000
) Spending ON C.CustomerID = Spending.CustomerID;

hard

sql

How can you optimize JOIN queries using indexes?

Create indexes on join columns for faster lookups.

CREATE INDEX idx_customerID ON Orders(CustomerID);

This improves join performance between Customers and Orders.

medium

sql

What are the differences between INNER JOIN with WHERE vs. OUTER JOIN?

INNER JOIN removes non-matching rows.
OUTER JOIN keeps non-matching rows but fills NULLs.

Example: Using WHERE incorrectly in a LEFT JOIN

SELECT C.Name, O.Amount
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE O.Amount > 5000;  --  This converts it into an INNER JOIN

Instead, filter inside JOIN condition

SELECT C.Name, O.Amount
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID AND O.Amount > 5000;

hard

sql

How do Common Table Expressions (CTEs) work with joins?

CTEs help break down complex joins into readable parts.

Example: Find employees earning above the department average

WITH AvgSalary AS (
    SELECT Department, AVG(Salary) AS DeptAvg
    FROM Employees
    GROUP BY Department
)
SELECT E.Name, E.Salary, A.DeptAvg
FROM Employees E
JOIN AvgSalary A ON E.Department = A.Department
WHERE E.Salary > A.DeptAvg;

medium

sql

What are the alternatives to joins for improving performance?

Denormalization (store combined data in one table)
Use EXISTS instead of IN
Use Materialized Views for precomputed joins
Index foreign keys

medium

sql

How do you handle NULL values in JOIN conditions?

Use COALESCE() to replace NULL values.

SELECT Customers.Name, COALESCE(Orders.Amount, 0) AS OrderAmount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

If a customer has no orders, it returns 0 instead of NULL.

medium

sql

What is denormalization, and how does it affect joins?

Denormalization reduces joins by storing redundant data, improving performance.

Example: Instead of joining Orders and Customers, store CustomerName in Orders.

ALTER TABLE Orders ADD COLUMN CustomerName VARCHAR(255);
UPDATE Orders O
JOIN Customers C ON O.CustomerID = C.CustomerID
SET O.CustomerName = C.Name;

medium

sql

What are the advantages and disadvantages of using joins vs. subqueries?

Joins: Faster for large datasets, better performance with indexes.
Subqueries: Simpler to read but can be slower.

Example: Fetching customer names using JOIN vs. Subquery

-- Using JOIN
SELECT C.Name, O.Amount
FROM Customers C
JOIN Orders O ON C.CustomerID = O.CustomerID;

-- Using Subquery
SELECT Name, (SELECT SUM(Amount) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) AS TotalSpent
FROM Customers;

hard

sql

How do Materialized Views help optimize JOIN queries?

Materialized Views store the result of a JOIN, making retrieval faster.

CREATE MATERIALIZED VIEW CustomerOrders AS
SELECT C.Name, O.Amount
FROM Customers C
JOIN Orders O ON C.CustomerID = O.CustomerID;

Instead of running the JOIN every time, querying the view is much faster.

medium

sql

operating-systems

What are the most common mistakes developers make when writing JOIN queries?

Common mistakes and fixes:

Forgetting indexes on join columns - CREATE INDEX idx_column ON Table(Column);
**Using SELECT *** instead of selecting only necessary columns
Using WHERE with LEFT JOIN incorrectly - Use conditions inside ON
Not handling NULL values properly

SQL Joins

Database-Specific Behavior

Discussion

Other categories