How to use SubQuery in SQL

Sometimes you can realize, one query is not enough to get the information you want.
It just feels like you need to do it "in steps". Thank god, we have Subqueries in SQL.

Subqueries will help you make more complex SQL queries and i will show you how to write them.

• Basic use

Using subqueries is pretty simple. Let's say, we have a list of customers:

What if you want to receive a list of customers which amount is higher than average of all amounts?

You would have 2 seperate queries:

# select customers
SELECT * FROM customers

# check average amount
SELECT AVG(amount) FROM customers

How to combine both queries and show only those customers which amount is higher than average?

You can use subqueries:

SELECT * FROM customers
WHERE amount > (SELECT AVG(amount) FROM customers)

As you can see, you can use one query inside another one. In this situation, the query included inside parenthesis will be called
as first one, and when we have result of this query, it will be compared with amount in parent query.

• Subqueries and IN operator

Instead of using logical operators, you can also use IN operator to match parent query with the list of results received from another call
(which can be included inside another table for example).

SELECT * FROM customers
SELECT name FROM users

# combine both queries
SELECT * FROM customers WHERE customer_name IN (SELECT name FROM users)

• Subqueries and EXISTS operator

With an use of subquery we can also check if the result of the subquery exists inside parent query.

We are using EXISTS operator in this case.

Let's say we have customers table and payments table. We want to get customers which have a payment equal or higher than 100.

It would look like that:

SELECT customer_name FROM customers as c
WHERE EXISTS
(SELECT * FROM payments as p
 WHERE p.customer_id = c.id
 AND amount >= 100)