Left and Right Join Results
Reported by candidates from Mastercard's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The mistake that sinks a first attempt on this Mastercard OA, reported in July 2024, is assuming a RIGHT JOIN output looks like the LEFT one flipped. It doesn't. You need both joins, labeled, stacked, and sorted with NULLs landing last. It's a SQL question about join semantics and duplicate keys, not a trick. If you blank on the UNION ALL layout or the ordering rule, StealthCoder runs invisibly during the live OA and gives you the query as a safety net. Read the column order and the sort spec carefully, because that's where points leak.
The problem
The tables left_items and right_items each contain uniquely identified rows and a shared join_key. Return the row-level output of both left_items LEFT JOIN right_items and left_items RIGHT JOIN right_items, using equality on join_key. Label rows from the first join as LEFT and rows from the second join as RIGHT. Preserve every matching row combination. For an unmatched row, return NULL for every column from the missing side. Tables left_items: left_id PK (Integer), join_key (Integer), left_value (Text) right_items: right_id PK (Integer), join_key (Integer), right_value (Text) Constraints left_id is unique in left_items, and right_id is unique in right_items. join_key is non-null; multiple rows may share the same key. Return columns in this order: join_type, join_key, left_id, left_value, right_id, right_value. Sort LEFT rows before RIGHT rows, then by join_key, left_id, and right_id ascending, with a missing ID after non-null IDs.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The pattern is two joins glued with UNION ALL. Write SELECT 'LEFT' AS join_type, l.join_key, l.left_id, l.left_value, r.right_id, r.right_value FROM left_items l LEFT JOIN right_items r ON l.join_key = r.join_key. Then the same shape with RIGHT JOIN and the label 'RIGHT'. Pitfall one: for unmatched right rows, join_key must come from the right table, so use r.join_key in that branch or COALESCE(l.join_key, r.join_key). Pitfall two: use UNION ALL, not UNION, since you must preserve every row combination and duplicate keys multiply rows. Pitfall three: ordering. LEFT sorts before RIGHT, which alphabetically works, then join_key, left_id, right_id, with NULLs last. Use ORDER BY join_type, join_key, (left_id IS NULL), left_id, (right_id IS NULL), right_id for portability. StealthCoder is your hedge if the NULL ordering slips your mind live.
Memorize the pattern. If you can't, run StealthCoder. The proctor sees the IDE. They don't see what's behind it.
You can drill Left and Right Join Results cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. Made by an engineer who treats the OA as theater. If yours is tonight, you don't have time to grind. You have time to hedge.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Mastercard's OA.
Mastercard reuses patterns across OAs. Made by an engineer who treats the OA as theater. If yours is tonight, you don't have time to grind. You have time to hedge. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Left and Right Join Results FAQ
What's the trick in the Left and Right Join Results question?+
Run both joins separately, label each, and stack them with UNION ALL. The trick is the right branch: the join_key must come from the right table for unmatched rows, or it comes back NULL and breaks your sort.
Why UNION ALL instead of UNION?+
UNION removes duplicate rows. The problem says to preserve every matching row combination, and join_key can repeat across rows. Dedupe could silently drop legitimate output rows, so UNION ALL is the only safe choice here.
How do I make NULL IDs sort after non-null ones?+
Engines differ on default NULL placement. Add an expression like (left_id IS NULL) before left_id in ORDER BY, which sorts false before true. Do the same for right_id. It works across MySQL, Postgres, and SQL Server without NULLS LAST syntax.
Does the sort by join_type work as is?+
Yes. The string 'LEFT' sorts before 'RIGHT' alphabetically, so ORDER BY join_type ascending puts LEFT rows first. Then you continue with join_key, left_id, and right_id in the order the problem lists them.
How do I prepare for this in 48 hours?+
Write a LEFT JOIN and a RIGHT JOIN on two small tables with duplicate keys by hand and predict row counts. Then practice stacking them with UNION ALL and writing a NULL-safe ORDER BY. Two or three reps cover everything this question tests.