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

Mastering SQL using Postgresql

Overview of Sub Queries¶