What is the difference between LEFT JOIN and RIGHT JOIN in SQL and when are they used?

LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there are no matches in the right table, the result will contain NULL for its columns.

RIGHT JOIN, on the other hand, returns all rows from the right table and the matching rows from the left, with NULLs where there are no matches.

LEFT JOIN is used when you want to get all data from the main (left) table and supplement it with data from the related table, while RIGHT JOIN is used when the main table is the right one.

Example:

SELECT a.id, b.value
FROM A a
LEFT JOIN B b ON a.id = b.a_id;

This will include all records from A, even if there are no matches in B.