DEV Community

Eda
Eda

Posted on • Originally published at rivea0.github.io

A simple example of a nested SQL query

#sql #beginners #learning

Originally published at https://rivea0.github.io/blog.

Let's say we have this table:

city country population latitude longitude
Guadalajara Mexico 1500800 20.659699 -103.349609
Toronto Canada 2795060 43.653226 -79.383184
Houston United States 2195914 29.760427 -95.369803
New York United States 8405837 40.712784 -74.005941
Philadelphia United States 1553165 39.952584 -75.165222
Havana Cuba 2106146 23.05407 -82.345189
Mexico City Mexico 8555500 19.432608 -99.133208
Phoenix United States 1513367 33.448377 -112.074037
Los Angeles United States 3884307 34.052234 -118.243685
Ecatepec de Morelos Mexico 1742000 19.601841 -99.050674
Montreal Canada 1717767 45.501689 -73.567256
Chicago United States 2718782 41.878114 -87.629798

We want to get all the cities west of Chicago.
As we go towards the west, the longitude must be less than the longitude of Chicago — the west of the prime meridian has longitudes that are negative numbers.

Here is the pseudocode:

SELECT * FROM north_american_cities
WHERE longitude < ${Chicago's longitude}
Enter fullscreen mode Exit fullscreen mode

So, what we need to do is somehow get the value of Chicago's longitude and use it as comparison.
It is by itself easy:

SELECT longitude FROM north_american_cities
WHERE city = 'Chicago';
Enter fullscreen mode Exit fullscreen mode

What we can do is simply put it inside the parenthesis and use it as a value itself, just like a variable:

SELECT * FROM north_american_cities
WHERE longitude < (
    SELECT longitude
    FROM north_american_cities
    WHERE city = 'Chicago'
);
Enter fullscreen mode Exit fullscreen mode

The result is this:

city country population latitude longitude
Guadalajara Mexico 1500800 20.659699 -103.349609
Houston United States 2195914 29.760427 -95.369803
Mexico City Mexico 8555500 19.432608 -99.133208
Phoenix United States 1513367 33.448377 -112.074037
Los Angeles United States 3884307 34.052234 -118.243685
Ecatepec de Morelos Mexico 1742000 19.601841 -99.050674

Furthermore, we can order the longitudes from west to east using the ORDER BY clause:

SELECT * FROM north_american_cities
WHERE longitude < (
    SELECT longitude
    FROM north_american_cities
    WHERE city = 'Chicago'
)
ORDER BY longitude;
Enter fullscreen mode Exit fullscreen mode

And, voilà:

city country population latitude longitude
Los Angeles United States 3884307 34.052234 -118.243685
Phoenix United States 1513367 33.448377 -112.074037
Guadalajara Mexico 1500800 20.659699 -103.349609
Mexico City Mexico 8555500 19.432608 -99.133208
Ecatepec de Morelos Mexico 1742000 19.601841 -99.050674
Houston United States 2195914 29.760427 -95.369803

The example is taken from sqlbolt.com.

Top comments (0)