Senior

What types of JOINs do you know and what are their differences?

sobes.tech AI

Answer from AI

Main types of JOIN in SQL and their differences:

INNER JOIN — returns only those records that have matches in both tables based on the join condition.
LEFT JOIN (LEFT OUTER JOIN) — returns all records from the left table and matching records from the right table. If there are no matches, the right table fields will be NULL.
RIGHT JOIN (RIGHT OUTER JOIN) — returns all records from the right table and matching records from the left table.
FULL JOIN (FULL OUTER JOIN) — returns all records from both tables, matching and non-matching, with NULLs in places where data is missing.
CROSS JOIN — Cartesian product of two tables, returning all possible combinations of rows.

Example:

-- INNER JOIN
SELECT * FROM A INNER JOIN B ON A.id = B.a_id;

-- LEFT JOIN
SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;

-- RIGHT JOIN
SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id;

-- FULL JOIN
SELECT * FROM A FULL JOIN B ON A.id = B.a_id;

-- CROSS JOIN
SELECT * FROM A CROSS JOIN B;

The choice of JOIN depends on what data needs to be retrieved and how to handle missing matches.

Previous Next