For an instructor lead, in-depth look at learning SQL click below.
Subqueries, in SQL, are a powerful and essential tool, used within the main SQL queries to form sophisticated queries and to deal with complex data and database structures. They are inner queries nested in a main, or outer query which may be Select, Update, Insert or Delete queries.
Understanding Subqueries
A subquery in SQL is also termed as inner query or nested query. It’s essentially a query within a query. By convention, a subquery is written within the parentheses and is executed first, and the result of it is used to complete the query condition for the main, or outer, query.
Types of Subqueries
In SQL, subqueries can generally be classified into two types:
- Single-row Subquery: where the inner query returns only one row.
- Multi-row Subquery: where the inner query returns multiple rows.
How to Use Subqueries in SQL
Subqueries can be used in various places within a main SQL query: in the SELECT, WHERE and FROM clauses. Now, let’s take a look at a couple of examples for a detailed understanding:
Subquery within a Select Statement
|
1 2 3 4 5 |
SELECT OrderId, (SELECT CustomerName FROM Customers WHERE Customers.CustomerId = Orders.CustomerId) as CustomerName FROM Orders; |
The above SQL code snippet explains the use of a subquery in a Select statement. This nested query generates a list of OrderId along with the respective CustomerName from two different tables (Orders, Customers) by matching the CustomerId.
Subquery within a Where clause
|
1 2 3 4 5 |
SELECT EmployeeName, Department FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees); |
The above SQL command uses a subquery within a WHERE clause. This subquery brings out the names and departments of employees whose salaries are greater than the average salary of all employees.
Conclusion
You can see that subqueries in SQL provide a powerful, flexible tool for creating complex queries and working with database structures and relationships. When you become comfortable with these nested queries, you’ll have a much broader range of options for sorting, filtering, and selecting the data you need for your applications and reports.