9 programs
Choose the best training program according to your career.
Improve the performance of your business with our innovative Industrial Training Program.
Read More →
Our instructor-led training classes are designed to make you an expert.
Read More →
Real-life case studies are a great way to demonstrate your practical knowledge.
Read More →
Explore blogs, latest technologies and industry updates with practical examples.
Read More →
Browse all available training programs and choose the right one for your career.
Read More →Know more about who we are and what we stand for.
Discover our mission to equip individuals with essential IT skills and industry expertise.
Read More →Learn, practice and plan your career — bilkul free.
Book a free video counselling session and pick the right course for you.
Read More →Download the SQL subqueries guide PDF from Uncodemy: scalar, IN, NOT IN, correlated and derived-table subqueries, plus join comparisons.
This page offers Uncodemy's SQL subqueries guide as a PDF download. A subquery is a query nested inside another. The guide explains scalar subqueries, IN and EXISTS, correlated subqueries and derived tables, shows when a join or a CTE is a better choice and points out the NOT IN trap with nulls.
Download the guide below. Each download is its own file, and everything in it is also explained on this page.
Scalar, IN, EXISTS, correlated and derived-table subqueries, the NOT IN trap with NULLs, subquery vs join vs CTE, performance notes and practice tasks, with examples on a customers and orders dataset.
A short form asks for your details before the download starts. Your details are used only to share study material and course information.
A subquery is a SELECT statement placed inside another statement, usually in brackets. The inner query runs to produce a value, a list of values or a table, and the outer query uses that result. Subqueries let you answer questions in two steps within a single statement, for example: find the orders whose total is above the average order total. The inner query calculates the average, and the outer query compares each order with it.
The examples use these tables: customers(customer_id, name, city, signup_date), orders(order_id, customer_id, order_date, status, order_total), order_items(order_id, product_id, quantity, unit_price) and products(product_id, product_name, category). Dates are text in YYYY-MM-DD form, so the examples run in SQLite and need small changes in other databases.
Each type returns something different and suits a different kind of question.
| Type | Returns | Typical use |
|---|---|---|
| Scalar subquery | One value | Compare each row with an overall average |
| Multi-row subquery with IN | A list of values | Filter by membership |
| EXISTS subquery | True if any row is found | Check whether related rows exist |
| Correlated subquery | Depends on the outer row | Compare each row with its own group |
| Derived table (in FROM) | A table | Aggregate first, then filter or join |
Orders whose total is above the average of all non-cancelled orders.
SELECT order_id, customer_id, order_total
FROM orders
WHERE status <> 'cancelled'
AND order_total > (SELECT AVG(order_total)
FROM orders
WHERE status <> 'cancelled')
ORDER BY order_total DESC;Customers who have placed at least one order, and customers who never have. Note the safer NOT EXISTS form below.
-- customers with at least one order
SELECT customer_id, name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
-- customers with no orders (NOT EXISTS is null-safe)
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);NOT IN behaves surprisingly if the subquery returns even one NULL. Because a comparison with NULL is unknown, not true, the whole NOT IN condition becomes unknown for every row and the query can return nothing at all, with no error.
If a column can be NULL, prefer NOT EXISTS, or add a filter such as WHERE column IS NOT NULL inside the subquery. This is a classic interview question and a real source of silent errors in reports.
Each order is compared with the average of that same customer's orders. The inner query refers to the outer row, so it runs logically once per outer row.
SELECT o.order_id, o.customer_id, o.order_total
FROM orders o
WHERE o.status <> 'cancelled'
AND o.order_total > (SELECT AVG(o2.order_total)
FROM orders o2
WHERE o2.customer_id = o.customer_id
AND o2.status <> 'cancelled');Aggregate first in a subquery, then join the result to another table.
SELECT c.city, t.orders, t.revenue
FROM (
SELECT customer_id, COUNT(*) AS orders, SUM(order_total) AS revenue
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
) t
JOIN customers c ON c.customer_id = t.customer_id
ORDER BY t.revenue DESC;Write queries for these questions:
Then rewrite each with a join or a CTE and compare which is clearer. Understanding several ways to express the same idea is what makes SQL feel natural.
| Situation | Prefer | Reason |
|---|---|---|
| Need columns from both tables in the result | Join | A subquery in WHERE cannot return its columns to the outer query |
| Only filtering by existence | EXISTS subquery | Stops at the first match and reads clearly |
| Compare with one aggregate value | Scalar subquery | Short and readable |
| Complex multi-step logic | CTE | Named steps are easier to read and debug |
| Find rows with no match | NOT EXISTS or LEFT JOIN with IS NULL | Avoids the NOT IN problem with NULL values |
Want to write SQL the way analysts do at work? Explore Uncodemy's Data Analytics course and learn from industry trainers.