Overview of Sub Queries

Let us understand details related to Sub Queries. We will also briefly discuss about nested sub queries.

  • We can have queries in from clause and such queries are called as sub queries.

  • Sub queries are commonly used with queries using analytic functions to filter the data further. We will see details after going through analytic functions as part of this section.

  • It is mandatory to have alias for the sub query.

  • Sub queries can also be used in WHERE clause with IN as well as EXISTS. As part of the sub query we can have join like conditions between tables in FROM clause of the main query and sub query. Such queries are called as Nested Sub Queries.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Note

Simplest example for a subquery

%%sql

SELECT * FROM (SELECT current_date) AS q

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
current_date
2020-12-01

Note

Realistic example for a subquery. We will get into details related to this query after covering analytic functions

%%sql

SELECT * FROM (
    SELECT nq.*,
        dense_rank() OVER (
            PARTITION BY order_date
            ORDER BY revenue DESC
        ) AS drnk
    FROM (
        SELECT o.order_date,
            oi.order_item_product_id,
            round(sum(oi.order_item_subtotal)::numeric, 2) AS revenue
        FROM orders o 
            JOIN order_items oi
                ON o.order_id = oi.order_item_order_id
        WHERE o.order_status IN ('COMPLETE', 'CLOSED')
        GROUP BY o.order_date, oi.order_item_product_id
    ) nq
) nq1
WHERE drnk <= 5
ORDER BY order_date, revenue DESC
LIMIT 20

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
20 rows affected.
order_date order_item_product_id revenue drnk
2013-07-25 00:00:00 1004 5599.72 1
2013-07-25 00:00:00 191 5099.49 2
2013-07-25 00:00:00 957 4499.70 3
2013-07-25 00:00:00 365 3359.44 4
2013-07-25 00:00:00 1073 2999.85 5
2013-07-26 00:00:00 1004 10799.46 1
2013-07-26 00:00:00 365 7978.67 2
2013-07-26 00:00:00 957 6899.54 3
2013-07-26 00:00:00 191 6799.32 4
2013-07-26 00:00:00 1014 4798.08 5
2013-07-27 00:00:00 1004 9599.52 1
2013-07-27 00:00:00 191 5999.40 2
2013-07-27 00:00:00 957 5699.62 3
2013-07-27 00:00:00 1073 5399.73 4
2013-07-27 00:00:00 365 5099.15 5
2013-07-28 00:00:00 1004 5599.72 1
2013-07-28 00:00:00 957 5099.66 2
2013-07-28 00:00:00 365 4799.20 3
2013-07-28 00:00:00 403 4419.66 4
2013-07-28 00:00:00 191 4299.57 5

Note

Multiple realistic examples for nested sub queries. You can see example with IN as well as EXISTS operators.

%%sql

SELECT * FROM order_items oi
WHERE oi.order_item_order_id 
    NOT IN (
        SELECT order_id FROM orders o
        WHERE o.order_id = oi.order_item_order_id
    )
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.
order_item_id order_item_order_id order_item_product_id order_item_quantity order_item_subtotal order_item_product_price 
%%sql

SELECT count(1) FROM order_items oi
WHERE oi.order_item_order_id 
    IN (
        SELECT order_id FROM orders o
        WHERE o.order_id = oi.order_item_order_id
    )
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
count
172198 
%%sql

SELECT * FROM order_items oi
WHERE NOT EXISTS (
        SELECT 1 FROM orders o
        WHERE o.order_id = oi.order_item_order_id
    )
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.
order_item_id order_item_order_id order_item_product_id order_item_quantity order_item_subtotal order_item_product_price 
%%sql

SELECT * FROM order_items oi
WHERE EXISTS (
        SELECT 1 FROM orders o
        WHERE o.order_id = oi.order_item_order_id
    )
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_item_id order_item_order_id order_item_product_id order_item_quantity order_item_subtotal order_item_product_price
1 1 957 1 299.98 299.98
2 2 1073 1 199.99 199.99
3 2 502 5 250.0 50.0
4 2 403 1 129.99 129.99
5 4 897 2 49.98 24.99
6 4 365 5 299.95 59.99
7 4 502 3 150.0 50.0
8 4 1014 4 199.92 49.98
9 5 957 1 299.98 299.98
10 5 365 5 299.95 59.99
Named Queries - Using WITH Clause CTAS - Create Table as Select