Let us understand details related to Sub Queries. We will also briefly discuss nested sub queries.
- We can have queries in the FROM clause, and such queries are called subqueries.
- Subqueries are commonly used with queries using analytic functions to filter the data further. We will see details after covering analytic functions.
- It is mandatory to have an alias for the subquery.
- Subqueries can also be used in the WHERE clause with IN as well as EXISTS. In the subquery, we can have join-like conditions between tables in the FROM clause of the main query and subquery. Such queries are called Nested Sub Queries.
-- Simplest example for a subquery
SELECT *
FROM (
SELECT current_date
) AS q
-- Realistic example for a subquery. We will get into details related to this query after covering analytic functions
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
-- Multiple realistic examples for nested subqueries. You can see examples with IN as well as EXISTS operators.
-- Example with IN operator
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
-- Example with EXISTS operator
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
-- Example using NOT EXISTS
SELECT *
FROM order_items oi
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.order_id = oi.order_item_order_id
)
LIMIT 10
-- Example using EXISTS
SELECT *
FROM order_items oi
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.order_id = oi.order_item_order_id
)
LIMIT 10