Can you list the main types of table joins in SQL and explain their differences?
sobes.tech AI
Answer from AI
Main types of JOINs in SQL and their differences:
-
INNER JOIN — returns only the rows with matches in both tables based on the join condition.
-
LEFT JOIN (LEFT OUTER JOIN) — returns all rows from the left table and matching rows from the right table. If there are no matches, the columns from the right table will be NULL.
-
RIGHT JOIN (RIGHT OUTER JOIN) — returns all rows from the right table and matching rows from the left table.
-
FULL JOIN (FULL OUTER JOIN) — returns all rows from both tables, matching and non-matching, with NULLs where there is no match.
-
CROSS JOIN — Cartesian product of two tables, returning all possible combinations of rows.
Example:
SELECT * FROM A INNER JOIN B ON A.id = B.a_id;
SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;
Thus, the choice of JOIN type depends on what data you want to retrieve: only matches, or all data from one table with possible NULLs from the other.