SQL Advanced 4: SUBQUERIES - In Select, From and Where
SQL
Subqueries, often referred to as the secret sauce of SQL, are versatile tools that enable us to extract and manipulate data in creative ways. In this brief yet informative blog post, we'll explore the magic of subqueries within the context of the SELECT, FROM, and WHERE statements, unraveling how they enhance our data querying capabilities.
Structured Query Language (SQL) serves as the backbone of database management, enabling us to retrieve, manipulate, and analyze data effortlessly. Among the various tools and techniques within SQL, subqueries stand out as powerful tools for extracting specific information from your database. In this beginner's guide, we'll dive into the world of subqueries, exploring how they function within the SELECT, FROM, and WHERE statements to help us master the art of querying data effectively!
Understanding Subqueries in SQL
A subquery, also known as a nested query or inner query, is a query within another query.
It serves as a supplementary query that aids in extracting data based on conditions specified in the main query. Subqueries offer flexibility by allowing us to retrieve data from one table and use it as a filtering condition in another.
We can use subqueries almost anywhere in a query such as SELECT, FROM, WHERE, INSERT, UPDATE, DELETE. But for today’s exercises, we are focusing on SELECT, FROM and WHERE statements.
Subqueries in the SELECT Statement in SQL
Subqueries in the SELECT statement enable us to retrieve data in combination with aggregated functions.
For instance, let’s use our ‘EmployeeSalary’ table – from SQL Basics 1 – to it calculate the average salary of all employees.
SELECT EmployeeID, Salary, (SELECT AVG(Salary) FROM EmployeeSalary)AS AVG_All
FROM EmployeeSalary
Before running the whole query, let’s run only the subquery:
SELECT AVG(Salary) FROM EmployeeSalary
If you like this content and want to learn more, you might also read:
SQL Basics 1: How to Create Table and Add Data in SSMS
SQL Basics 2: SELECT + FROM Statements
SQL Basics 4: GROUP BY and ORDER BY Statements
SQL Intermediate 1: INNER and OUTER Joins
SQL Intermediate 2: UNION Operator
SQL Intermediate 3: CASE Statement
SQL Intermediate 4: HAVING Clause
SQL Intermediate 5: UPDATE and DELETE Data
SQL Intermediate 6: AS - Aliasing
SQL Intermediate 7: PARTITION BY Clause
SQL Advanced 1: CTEs - Common Table Expressions
SQL Advanced 3: Stored Procedures
SQL Advanced 4: SUBQUERIES - In Select, From and Where
SQL Advanced 5: String Functions - TRIM, LTRIM, RTRIM, REPLACE, SUBSTRING, UPPER, and LOWER
The outcome is ONLY the average salary of all employees.
When we run the whole query:
The outcome shows us the EmployeeID, Salary, and AVG_All columns.
Subqueries in the FROM Statement in SQL
Subqueries in the FROM statement, also known as derived tables or inline views, enable us to create temporary tables for our main query. These subqueries are used to simplify complex queries by breaking them into manageable parts.
For instance, imagine we want to find out the average salary for all employees and also see each employee's ID alongside the average for reference:
SELECT Sal.EmployeeID, AVG_All
FROM (SELECT EmployeeID, Salary, AVG(Salary) OVER() AS AVG_All
FROM EmployeeSalary) AS Sal
So, when we run this query, we get a list of employees' IDs along with the average salary that applies to all employees. It's a bit like looking at each employee's salary in comparison to what the average salary for everyone is. This kind of query can help us understand how each employee's salary relates to the overall average.
NOTE: Temp tables and CTEs might be better for this type of situation but good to show how it can work.
Subqueries in the WHERE Statement in SQL
Subqueries in the WHERE statement allow you to filter results based on data retrieved from another table. These subqueries assist in refining our queries to match specific criteria.
For example, we have information about employees like their IDs, job titles, and salaries in our ‘EmployeeSalary’ table. We also have another set of data that tells us about their ages in our ‘EmployeesDemographics’ table. Now, we want to find out details about employees who are older than 50 years, without using JOIN.
SELECT EmployeeID, JobTitle, Salary
FROM EmployeeSalary
WHERE EmployeeID IN ( SELECT EmployeeID
FROM EmployeeDemographics
WHERE Age > 50)
The query helps you find the job titles and salaries of employees who are older than 50 years. It's like filtering out the information you need from two sets of data: one set with ages and another set with job details and salaries.
NOTE: In subquery we can only have one column selected, so we can’t select EmployeeID and Age, for example. If we wanted to display the Age as a column, we would have to use JOIN to be able to select more columns from the ‘EmployeeDemographics’ table – subquery table.
Benefits and Considerations Of Subqueries in SQL
Subqueries offer several benefits, such as enhancing query flexibility, enabling complex data extraction, and breaking down complex queries into manageable parts. However, it's important to consider the performance implications of subqueries, as they can impact query execution times. In some cases, using joins might provide better performance, like the above example.
Subqueries are a fundamental concept in SQL that allows you to extract and manipulate data in creative and powerful ways. By understanding how subqueries work within the SELECT, FROM, and WHERE statements, you can unlock the ability to solve complex data retrieval problems efficiently. As you embark on your SQL journey, remember that practicing with subqueries will not only refine your querying skills but also empower you to harness the full potential of your database management capabilities.