In this medium post, we will be learning how to work with multiple tables using subqueries. Previously we have only worked with one table.
Learning Objectives.
- Define subqueries.
- Discuss advantages and disadvantages.
- Explain how subqueries can help us merge data from two or more tables.
- Write more efficient subqueries.
What Are Subqueries?
- Queries embedded into other queries.
- Relational databases store data in multiple tables.
- Subqueries merge data from multiple sources together.
- Helps with adding other filtering criteria.
Problem: Subqueries to Filter.
Let’s say we need to know the region of each customer and find customers who have had an order with freight over 100.
We would need to:
- Retrieve all customer IDs for orders with freight over 100.
- Retrieve customer information.
- Combine the two queries.
This would actually require us to access two separate tables in this situation which means two different queries need to be written.
However, we could just use a subquery:
SELECT
CustomerID,
CompanyName,
Region
FROM Customers
WHERE CustomerID in (SELECT CustomerID
From Orders
WHERE Freight > 100);Working with Subquery Statements.
Always perform the innermost SELECT portion first.
The DBMS is performing two operations here:
- Getting the order numbers for the product selected.
- Adding that to the WHERE clause and processing the overall SELECT statement.
