SQL Joins Interview Questions

A join in SQL Server is a way to combine rows from two or more tables based on a related column between them. It lets you query data spread across multiple tables as if it were a single result set. SQL Server supports several types: INNER JOIN, LEFT/RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN, each controlling which rows are included when there is or is not a match.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

An INNER JOIN returns only the rows where there is a matching value in both tables based on the join condition. If a row in the left table has no corresponding row in the right table, it is completely excluded from the result, and the same goes for the right table. It is the most commonly used join type and the default when you just write JOIN without specifying a type.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
-- Only employees with a valid department are returned

A RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and only the matching rows from the left table. Where there is no match in the left table, the result shows NULL for those columns. It is essentially the mirror image of a LEFT JOIN. In practice, most developers rewrite RIGHT JOINs as LEFT JOINs by swapping the table order because it tends to be easier to read, but functionally they are identical.

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
-- All departments are returned, even those with no employees
-- e.name will be NULL for departments with no staff

A self-join is when you join a table to itself, treating it as if it were two separate tables by using aliases. It's useful for querying hierarchical or relational data within a single table, such as finding employees and their managers when both are stored in the same employees table, or comparing rows within the same table.

SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id;

A cross join returns the Cartesian product of two tables, meaning every row from the first table is combined with every row from the second table. If table A has 10 rows and table B has 20 rows, the result has 200 rows. There is no ON condition because you're intentionally pairing every combination. Cross joins are used for generating combinations, test data, or calendar/date grid scenarios, but accidental cross joins on large tables can be catastrophic for performance.

SELECT a.product_name, b.color
FROM products a
CROSS JOIN colors b;

A JOIN combines rows from two or more tables based on a related column between them. The main types are INNER JOIN, which returns only rows with matching values in both tables; LEFT JOIN, which returns all rows from the left table and matched rows from the right; RIGHT JOIN, which does the opposite; and FULL OUTER JOIN, which returns all rows from both tables regardless of matches. There's also CROSS JOIN, which produces a cartesian product of both tables, and SELF JOIN, where a table is joined to itself.

-- INNER JOIN: only matched rows
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- LEFT JOIN: all employees, even those without a department
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

A FULL JOIN, or FULL OUTER JOIN, returns all rows from both tables regardless of whether a match exists on the other side. Where there is no match, the missing side's columns come back as NULL. This is useful when you want to see everything from both tables and identify where records exist on one side but not the other. It is essentially a LEFT JOIN and a RIGHT JOIN combined into one result set.

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
-- Rows with no matching department show NULL for department_name
-- Departments with no employees show NULL for employee name