SQL Inner vs Outer Join | Dataford Interview Questions

Explain SQL inner join vs outer join.

Asked in the Phone round 2 stage. Translated.

Using the destinations and reviews tables, write a query that demonstrates outer-join behavior by preserving rows that lack a match in either table.

Output

Return destination_id, destination_name, review_id, review_destination_id, rating, and join_status.
Include matched rows and unmatched rows from both tables. Label rows as matched, destination_only, or review_only.
Order by matched or destination-only rows first, ascending destination_id, then review_id; place review-only rows last.

Schema

destinations

Column	Type	Description
destination_idPK	INT	Unique destination identifier
destination_name	VARCHAR(100)	Name of the destination
country_code	VARCHAR(2)	Two-letter destination country code

reviews

Column	Type	Description
review_idPK	INT	Unique review identifier
destination_id	INT	Destination referenced by the review
rating	INT	Review rating from one to five
review_title	VARCHAR(150)	Short review title

Your question is SQL Inner vs Outer Join. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem