Semi Join Exists — Solution & ExplanationProblems / Semi Join Exists / EditorialApproach

Use WHERE EXISTS (SELECT 1 FROM shipments WHERE shipments.supplier_id = suppliers.supplier_id) to check each supplier for at least one shipment.

EXISTS short-circuits — it stops scanning as soon as it finds a match.

Unlike an INNER JOIN, EXISTS naturally avoids duplicates.

Key InsightA semi-join returns rows from the left table that have at least one match in the right table, without duplicating the left row. EXISTS in SQL and left_semi in PySpark both implement this. Suppliers 3 (Circuit Co), 5 (Echo Parts), and 7 (Globe Trade) have no shipments.Hints

EXISTS returns TRUE as soon as it finds one matching row — it's efficient.

Unlike INNER JOIN, EXISTS won't duplicate the supplier row even if they have multiple shipments.

In PySpark, 'left_semi' join type achieves the same result.

Solve Semi Join Exists yourself →