What is the difference between INNER JOIN and LEFT/RIGHT JOIN?

INNER JOIN returns only those rows that have matches in both tables based on the join condition. If there are no matches, the row will not be included in the result.

LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If there is no match for a row from the left table, the fields from the right table will be filled with NULL.

RIGHT JOIN (or RIGHT OUTER JOIN) — conversely, returns all rows from the right table and the matching ones from the left, with NULL where there are no matches.

Example:

SELECT * FROM A INNER JOIN B ON A.id = B.a_id; -- only matches
SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;  -- all from A + matches from B
SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id; -- all from B + matches from A

Thus, INNER JOIN filters by matches, while LEFT/RIGHT JOIN keep all rows from one of the tables, supplementing them with data from the other if there are matches.