Sub-query vs INNER JOIN

Often the results from a correlated sub-query can be replicated using an INNER JOIN. Depending on what your requirements are, using an INNER JOIN may be more efficient because it only makes one pass through the data whereas the correlated sub-query must execute for each row in the outer query.

You want to find out the 2017 population of the biggest city for every country in the world. You can get this information from the Earthquakes database with the Nations table as the outer query and Cities table in the sub-query.

You will first create this query as a correlated sub-query then rewrite it using an INNER JOIN.

Diese Übung ist Teil des Kurses

Improving Query Performance in SQL Server

Kurs anzeigen

Interaktive Übung

Versuche dich an dieser Übung, indem du diesen Beispielcode vervollständigst.

SELECT
	n.CountryName,
	 (SELECT MAX(c.___) -- Add 2017 population column
	 FROM Cities AS c 
                       -- Outer query country code column
	 WHERE c.CountryCode = n.___) AS BiggestCity
FROM ___ AS n; -- Outer query table

Code bearbeiten und ausführen