Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A data analyst needs to query data from two tables, 'Sales' and 'Products', in an Azure SQL Database. The analyst wants to retrieve all sales records along with matching product information where available, but also wants to see sales records even if there is no corresponding product information. Which type of JOIN operation should the analyst use?
- AFULL OUTER JOIN
- BINNER JOIN
- CRIGHT JOIN
- DLEFT JOIN
Show answer & explanationAnswer & explanation
Correct answer: D. LEFT JOIN
A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table ('Sales' in this case) and the matching rows from the right table ('Products'). If there is no match, NULLs are returned for the columns from the right table. This fulfills the requirement to see all sales records regardless of a product match.
Why the other options are wrong
- A. FULL OUTER JOIN returns all rows when there is a match in one of the tables, including unmatched rows from both, which is more than what was requested (all sales, with or without product match).
- B. INNER JOIN returns only the rows that have matching values in both tables, excluding sales without product info.
- C. RIGHT JOIN returns all rows from the right table and matched rows from the left table, which would show all products, not all sales.
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, NULLs are returned for right table columns.
- Returns all rows from the left table
- Returns matching rows from the right table
- NULLs for unmatched right table columns
- Also known as LEFT OUTER JOIN
Memory trick: Joins connect tables: inner, left, right, full.