Bird
0
0

You have two tables:

hard📝 Application Q15 of 15
SQL - Advanced Joins
You have two tables:

Products:
ProductID | Name
1 | Pen
2 | Pencil
3 | Eraser

Sales:
ProductID | Quantity
2 | 100
3 | 50
4 | 10

You want a query to list all products and sales quantities, including products with no sales and sales for unknown products.

Which query correctly achieves this?
ASELECT Products.ProductID, Products.Name, Sales.Quantity FROM Products FULL OUTER JOIN Sales ON Products.ProductID = Sales.ProductID;
BSELECT Products.ProductID, Products.Name, Sales.Quantity FROM Products LEFT JOIN Sales ON Products.ProductID = Sales.ProductID;
CSELECT Products.ProductID, Products.Name, Sales.Quantity FROM Sales RIGHT JOIN Products ON Sales.ProductID = Products.ProductID;
DSELECT Products.ProductID, Products.Name, Sales.Quantity FROM Products INNER JOIN Sales ON Products.ProductID = Sales.ProductID;
Step-by-Step Solution
Solution:

  1. Step 1: Identify requirement for all products and all sales

    We want all products even if no sales, and all sales even if product unknown.

  2. Step 2: Choose join type

    FULL OUTER JOIN returns all rows from both tables, matching where possible, filling NULLs otherwise.

  3. Step 3: Check options

    SELECT Products.ProductID, Products.Name, Sales.Quantity FROM Products FULL OUTER JOIN Sales ON Products.ProductID = Sales.ProductID; uses FULL OUTER JOIN correctly; others exclude unmatched rows from one side.

  4. Final Answer:

    SELECT Products.ProductID, Products.Name, Sales.Quantity FROM Products FULL OUTER JOIN Sales ON Products.ProductID = Sales.ProductID; -> Option A

  5. Quick Check:

    FULL OUTER JOIN = all products and sales [OK]
Quick Trick: Use FULL OUTER JOIN to include all rows from both tables [OK]
Common Mistakes:
MISTAKES
  • Using LEFT JOIN excludes sales without products
  • Using INNER JOIN excludes unmatched rows
  • Confusing RIGHT JOIN direction with LEFT JOIN

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More SQL Quizzes
GROUP BY and HAVING - GROUP BY multiple columns - Quiz 15hard INNER JOIN - Joining on primary key to foreign key - Quiz 13medium INNER JOIN - How the join engine matches rows - Quiz 8hard LEFT and RIGHT JOIN - Finding unmatched rows with LEFT JOIN - Quiz 8hard LEFT and RIGHT JOIN - Finding unmatched rows with LEFT JOIN - Quiz 9hard Subqueries - Nested subqueries - Quiz 12easy Table Relationships - Many-to-many with junction tables - Quiz 5medium Table Relationships - Referential integrity enforcement - Quiz 10hard Table Relationships - ER diagram to table mapping - Quiz 12easy Views - Dropping and altering views - Quiz 5medium