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 customers who have placed an order but also want to include customers who have not placed any orders. The result should show customer details and any associated order details where they exist. Which type of JOIN operation should be used?
- ARIGHT JOIN
- BLEFT JOIN
- CINNER JOIN
- DFULL OUTER JOIN
Show answer & explanationAnswer & explanation
Correct answer: B. LEFT JOIN
A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table (Customers) and the matching rows from the right table (Orders). If there is no match, NULLs are returned for the right table's columns, thus including customers without orders.
Why the other options are wrong
- A. RIGHT JOIN returns all rows from the right table (Orders) and matching rows from the left table (Customers), which would exclude customers without orders.
- C. INNER JOIN returns only the rows where there is a match in both tables, excluding customers without orders.
- D. FULL OUTER JOIN returns all rows when there is a match in either the left or right table, which would include orders without customers (if possible) and is more than what's requested.
LEFT JOIN
A LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left table, and the matching records from the right table. If there is no match, NULLs are returned for the right side.
- Includes all rows from the 'left' table.
- Includes matching rows from the 'right' table.
- Returns NULL for right-table columns if no match.
Memory trick: Left for All Left, Right for All Right, Inner for Only Match, Full for All.