What is the difference between LEFT JOIN and RIGHT JOIN?

LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the "left" table (the first table in the FROM clause) and the matching rows from the "right" table. If there are no matches in the "right" table, NULL values are returned for columns from the "right" table.

RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the "right" table (the second table in the FROM clause) and the matching rows from the "left" table. If there are no matches in the "left" table, NULL values are returned for columns from the "left" table.

Essentially, a RIGHT JOIN can always be replaced with a LEFT JOIN by swapping the tables.

Example:

Table users:

id	name
1	Alice
2	Bob
3	Carol

Table orders:

user_id	amount
1	100
1	150
2	200

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Result of LEFT JOIN:

name	amount
Alice	100
Alice	150
Bob	200
Carol	NULL

SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

Result of RIGHT JOIN:

name	amount
Alice	100
Alice	150
Bob	200