Which Join Returns All Rows in Both Tables?
Short answer
B Use a full outer join to return all rows from both tables in a merge query.
Question
Key Takeaways
The question tests knowledge of SQL join types and the common trap is confusing partial outer joins (left/right) with a full outer join that preserves all rows from both sides.
To return all rows from both tables in a Fabric lakehouse visual query, a full outer join is required. This page explains why full outer join is the correct choice over inner, left, or right joins.
Common mistake: Choosing left outer or right outer, which only return all rows from one table, not both.
Expert Analysis
Why the Answer Is Correct
A full outer join combines the results of both left and right outer joins. It returns all rows from both the left and right tables, filling missing data with NULLs where there is no match. This directly satisfies the requirement to return all rows in both tables.Why the Other Options Are Wrong
An inner join (A) only returns rows where there is a match in both tables. Left outer (C) and right outer (E) return all rows from only one table and matched rows from the other. Right anti (D) returns only rows from the right table that have no match in the left table.Community Comment Notes
The community strongly agrees on the full outer join, noting it is a fundamental SQL concept. As stilferx noted, "FULL OUTER JOIN. Because - ALL rows, not from left, right,... table(s)", and vernillen added that other joins "wouldn't return all the rows in both tables but exclude some."Official Reference
https://www.w3schools.com/sql/sql_join.asp
This link opens the official vendor documentation in a new tab.
Exam Strategy
When a question specifies returning all rows from both tables without exclusions, immediately look for 'full outer join'. Eliminate inner and anti joins first, then distinguish between left/right and full.
Community Votes
100% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.
Discussion 6 contributions
Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors, not of islever. The comments shown first are the most substantive of the thread — ranked by depth, community endorsement and relevance; expand the list below to read the rest.
From public community threads 6
-
VAzureD 👍 4 Answered BFULL (OUTER) JOIN https://www.w3schools.com/sql/sql_join.asp
-
2dc6125 👍 3B. Left or right will only return one table all rows and other table if exist in the other table. inner will only return if data match in both tables so Full (outer) join will return all data from both tables
-
stilferx 👍 3 Answered BIMHO, 1st grage of SQL school - FULL OUTER JOIN. Because - ALL rows, not from left, right, ... table(s)
-
Nefirs 👍 2 Answered Bfull outer keeps rows from both sides
-
vernillen 👍 1 Answered BJust making it more obvious, but yea... all other options wouldn't return all the rows in both tables but exclude some.
Show 1 more comment
-
nappi1 👍 1 Answered Bsince there is no other information on the tables, the answer is the academical one
Reader comments & corrections
No reader corrections yet — spotted an error or have a note? Share it below.
Related Analysis
Practice All DP-600 Questions
Access 115 questions with complete answers and detailed explanations.
View Full DP-600 Practice Test →