Describe the four common SQL JOIN types and when you’d use them.

• A. INNER JOIN returns matches.
• B. LEFT JOIN includes non-matching rows from the left side.
• C. RIGHT JOIN includes non-matching rows from the right side.
• D. FULL OUTER JOIN returns all matches and non-matches.

Answer: (D)

Joining tables combines related data by matching rows on a shared key, and the four common types differ in how they handle rows that don’t have a counterpart on the other side. An inner join keeps only the rows where there is a match in both tables, so you get data that exists on both sides and nothing extra. This is ideal when you only want records that have related information in both sources, such as listing customers who actually placed orders with their order details.

A left join brings in every row from the left table and the matching rows from the right table; if there’s no match, the right-side fields appear as null. Use this when you need all records from the left table regardless of whether there’s related data on the right—for example, showing all customers and their orders, including customers who haven’t placed any orders yet.

A right join is the mirror image: it preserves all rows from the right table and attaches matching data from the left, with nulls where there’s no left match. It’s often used when you want completeness on the right-hand side and can be thought of as swapping the table order and using a left join instead.

A full outer join returns all rows from both tables, filling in nulls where there’s no match. This gives a complete view of both sides, including items that have no counterpart in the other table, which is useful for reconciliation tasks or when you want to see gaps between two datasets.