SUB-QUERIES

In the previous section, we looked at the WITH statement with allow us to query the output of another query. Sub-queries (in this case, in-line subqueries) allow us to do the same thing. We have the ability to query the results of another query by simply stacking queries together.

For example, we can write a query that returns all records from the orders table where the freight load is greater than 20, and on higher query only returns the order_id, ship_via, ship_country, freight . Using sub-queries, the full query would look like this:

select order_id, ship_via, ship_country, freight
from
( select *
from orders where freight >= 20 )
order by freight
limit 10

| order_id | ship_via | ship_country | freight |
| -------- | -------- | ------------ | ------- |
| 3017     | 1        | Netherlands  | 21.6    |
| 3005     | 2        | Canada       | 22.8    |
| 3025     | 2        | Portugal     | 23.6    |
| 3019     | 3        | Germany      | 24.7    |
| 3001     | 1        | Canada       | 25.5    |
| 3012     | 2        | France       | 26.85   |
| 3022     | 1        | null         | 27.8    |
| 3007     | 3        | Germany      | 28.9    |
| 3030     | 1        | Finland      | 29.85   |
| 3016     | 3        | France       | 29.95   |

SUB-QUERIES as Filters

Another use case for using sub-queries is as filters to our main query. Let's demonstrate this with an example.

Suppose we have two tables: orders and shipping. We want to filter the orders using a query the returns shipping courier. In this case, we are interested in the shipping courier with Ids 1 and 2. We can implement this in the query form below:

select customer_id, last_name, city, country
from customers
where country in ( select ship_country from orders where freight >= 20 )

| customer_id | last_name    | city       | country     |
| ----------- | ------------ | ---------- | ----------- |
| 2001        | Johnson      | New York   | USA         |
| 2003        | Brown        | Toronto    | Canada      |
| 2005        | Miller       | Paris      | France      |
| 2006        | Wilson       | Berlin     | Germany     |
| 2008        | Taylor       | Madrid     | Spain       |
| 2009        | Anderson     | Rome       | Italy       |
| 2010        | Thomas       | Amsterdam  | Netherlands |
| 2011        | Jackson      | Stockholm  | Sweden      |
| 2013        | Garcia       | Barcelona  | Spain       |
| 2027        | Schmidt      | Munich     | Germany     |
| 2029        | Fernandez    | Madrid     | Spain       |
| 2030        | Rossi        | Milan      | Italy       |
| 2031        | Dubois       | Lyon       | France      |
| 2033        | Van Der Berg | Amsterdam  | Netherlands |
| 2036        | Andersson    | Stockholm  | Sweden      |
| 2045        | Petersen     | Helsinki   | Finland     |
| 2046        | Johansson    | Gothenburg | Sweden      |
| 2050        | Lindqvist    | Malmo      | Sweden      |

Notice that the output is now filtered for only customers whose country is present in the orders with freight greater than 20.

Using sub-queries is important especially when you are working with multiple tables and performing more complex operations against them. We will see more of this on advanced topics in the intermediate section.