WITH

Often times we may need to use the results of one query as a table that we can run a query on. This is very useful if you are trying to run multiple queries that may slice the same output of a general query in multiple different ways. SQL allows for running sub-queries to solve this exact problem. Sub-querying is simply running a query on top of the result of another query. One way to do this is to use the WITH statement.

The WITH statement allows us to store the output of queries as tables in an alias that we can query on. If you have experience with programming, of this as assigning the output of the query to a variable and being able to use that variable as a table in another query. Let's demonstrate this in an example.

Let's query the Customer table and select some basic information about our customers from india including customer_id, email, city, country. We will store the results of this query into an alias name customer_data using the WITH statement. We then query customer_data to only return customers from Spain. This is how the query would look like.

with customer_data as (
    select *
    from customers
    where country = 'India'
)

select customer_id, email, city, country
from customer_data

| customer_id | email                  | city      | country |
| ----------- | ---------------------- | --------- | ------- |
| 2021        | [email protected]    | Mumbai    | India   |
| 2022        | [email protected] | Delhi     | India   |
| 2023        | [email protected]  | Bangalore | India   |
| 2024        | [email protected] | Kolkata   | India   |

Notice that the output contains information about the customers from india is queried fully in the first alias but queried from the customer_data alias.

Multiple WITH Sub-Queries

Just as we have created an alias customer_data above with the WITH statement, we can create as many of these alias tables as we need. To do this, we only separate the aliases with a comma. In the example below, we add another alias table orders_data that is all records from the employee table. The query looks like below:

With customer_data as (
    select *
    from customers
    where country NOT LIKE '______%'
), orders_data as (
    select *
    from orders  )

select *
from orders_data
where ship_country in (select country from customer_data)

| order_id | customer_id | ship_country | ship_via | freight | order_date |
| -------- | ----------- | ------------ | -------- | ------- | ---------- |
| 3004     | 2004        | USA          | 3        | 45      | 2023-02-01 |
| 3006     | 2006        | UK           | 1        | 15.6    | 2023-02-10 |
| 3009     | 2009        | Spain        | 1        | 35.75   | 2023-02-25 |
| 3013     | 2013        | Italy        | 3        | 38.5    | 2023-03-15 |
| 3021     | null        | Spain        | 2        | 31.45   | 2023-04-20 |

So we can now use employee_data and customer_data as tables to run queries on top.

The use of subqueries will be important the more advanced you get. Often times when dealing with multiple tables that you may need to join at variables labels, the WITH statement with allowing for clean, readable, and efficient queries. In the next section, we look at implementing in-line sub-queries.