Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A data team is working with an Azure SQL Database. They need to retrieve all product names and their associated category names. Some products might not have a category assigned, and these products should still appear in the result set with a NULL value for the category name. Which type of JOIN should be used?
- AFULL OUTER JOIN
- BRIGHT JOIN
- CINNER JOIN
- DLEFT JOIN
Show answer & explanationAnswer & explanation
Correct answer: D. LEFT JOIN
A LEFT JOIN returns all rows from the left table (Products) and the matching rows from the right table (Categories). If there is no match, it returns NULL for the columns from the right table, which satisfies the requirement to include products without categories.
Why the other options are wrong
- A. FULL OUTER JOIN would return all products and all categories, including those with no matches from either side, which is broader than needed.
- B. RIGHT JOIN would return all categories and their matching products, or NULLs if no product, which is not what was asked.
- C. INNER JOIN would only return products that have a matching category, excluding products without categories.
LEFT JOIN
A type of SQL JOIN that returns all rows from the left table, and the matching rows from the right table. If there is no match in the right table, NULL values are returned for the columns from the right table.
- Also known as LEFT OUTER JOIN.
- Preserves all rows from the 'left' table.
- Commonly used when you want to see all items from one list, even if they don't have a corresponding item in another.
Memory trick: Left Join: Left side is always right.