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:
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 )
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.