Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A data engineer is working with an Azure SQL Database. They need to retrieve all customer records along with any orders they have placed. If a customer has not placed any orders, their information should still be included in the result set, with NULLs for the order-related columns. Which type of JOIN should the engineer use?
- AFULL OUTER JOIN
- BLEFT JOIN
- CINNER JOIN
- DRIGHT JOIN
Show answer & explanationAnswer & explanation
Correct answer: B. LEFT JOIN
A LEFT JOIN returns all rows from the left table (customers in this case) and the matching rows from the right table (orders). If there is no match in the right table, NULLs are returned for the right table's columns, fulfilling the requirement to include all customers even without orders.
Why the other options are wrong
- A. FULL OUTER JOIN returns all rows when there is a match in either the left or right table, including customers without orders and orders without customers, which is more than what was requested (only all customers and their orders).
- C. INNER JOIN returns only the rows that have matching values in both tables, which would exclude customers without orders.
- D. RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and the matched rows from the left table. This would prioritize orders, not all customers.
LEFT JOIN
A SQL JOIN type that returns all rows from the left table, and the matching rows from the right table. If no match is found for a left table row, NULLs are returned for the right table's columns.
- Includes all rows from the 'left' table.
- Includes matching rows from the 'right' table.
- Returns NULLs for right table columns if no match.
- Useful when you want to see all entries from one table, regardless of matches in another.
Memory trick: Left loves all its own, Right loves all its own, Inner only loves matches, Full loves all.