Bird
0
0

Given these tables:

medium📝 query result Q4 of 15
SQL - Advanced Joins
Given these tables:

Table A:
ID | Name
1 | Alice
2 | Bob

Table B:
ID | City
2 | Paris
3 | Tokyo

What will be the result of this query?
SELECT A.ID, A.Name, B.City FROM A FULL OUTER JOIN B ON A.ID = B.ID ORDER BY A.ID, B.ID;
A1 | Alice | NULL<br>2 | Bob | NULL<br>3 | NULL | Tokyo
B1 | NULL | NULL<br>2 | Bob | Paris<br>3 | NULL | Tokyo
C1 | Alice | NULL<br>2 | Bob | Paris<br>3 | NULL | Tokyo
D1 | Alice | NULL<br>2 | Bob | Paris
Step-by-Step Solution
Solution:

  1. Step 1: Match rows on ID

    ID 2 is common in both tables, so row with ID 2 will have Name and City.

  2. Step 2: Include unmatched rows from both tables

    ID 1 is only in A, so City is NULL. ID 3 is only in B, so Name is NULL.

  3. Final Answer:

    1 | Alice | NULL
    2 | Bob | Paris
    3 | NULL | Tokyo     -> Option C

  4. Quick Check:

    FULL OUTER JOIN includes all rows with NULLs where unmatched = B [OK]
Quick Trick: FULL OUTER JOIN keeps all rows, fills NULLs for missing matches [OK]
Common Mistakes:
MISTAKES
  • Forgetting unmatched rows from either table
  • Assuming INNER JOIN behavior

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