A
Joins are one of the most important SQL concepts because real datasets are usually split across multiple tables. Interviewers often ask this to check whether you understand both result shape and business meaning.
Explain the main types of joins in SQL and how they differ. Your answer should cover:
Keep the explanation practical and interview-focused. You should be able to describe INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN, and mention common mistakes such as filtering a LEFT JOIN in a way that turns it into an INNER JOIN.
An INNER JOIN returns only rows where the join condition matches in both tables. If a row exists in one table but not the other, it is excluded from the result.
SELECT c.customer_name, o.order_id
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;
A LEFT JOIN returns all rows from the left table and matching rows from the right table. If there is no match, the right-side columns are returned as NULL.
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
A RIGHT JOIN keeps all rows from the right table, while a FULL OUTER JOIN keeps all rows from both tables. These joins are useful when you need to preserve unmatched rows from one side or both sides.
SELECT c.customer_name, o.order_id
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;
A CROSS JOIN returns the Cartesian product of two tables, meaning every row from the first table is paired with every row from the second. It is useful for generating combinations but can grow very quickly.
SELECT s.size_name, col.color_name
FROM sizes s
CROSS JOIN colors col;
A SELF JOIN joins a table to itself, usually with aliases, to compare rows within the same table. It is commonly used for hierarchical relationships such as employees and managers.
SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;
