Copyright

SQL Subqueries: Example & Definition

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

We live in a world of data: It's no longer uncommon to see databases that exceed ten million lines of information! Getting data out of large databases can be challenging. Enter the subquery. This lesson will define a method for filtering results to a manageable level and provide working examples.

Subqueries

Sometimes it feels like we have to work with too much data. With the growth of databases, getting data out of those databases can be challenging. We need some sort of a filter to get the results we want, especially from larger sets of data.

Think of a large company, with data for employees and their benefit plans. Let's say they are stored in tables labeled employee and benefits. If you are asked to query the data to get only employees enrolled in a Health plan, you could write two queries, then copy the data somewhere else, and perform a comparison.

That's way too much work!

Instead, let's write a subquery. Think of this as a filter. It further refines the results to what is truly needed. Sometimes subqueries are called nested queries, because they are nestled within the main SQL query.

You will also see the term INNER SELECT used for a subquery. Again, this is because it is nested inside the main SQL selection statements.

A basic SQL query contains some standard components: SELECT, FROM, and WHERE. There are two update statements also: UPDATE (editing an existing record) and INSERT (creating a new entry in the database).

Subqueries can be used in any of these. We'll start with the more common uses and go from there.

WHERE

In our first example, let's take a look at our original question: How to get only employees in the Health plan without having to perform extra steps? The SQL example below shows the subquery nested within the WHERE clause. The code first begins by selecting employees, then performs the filter/subquery on the benefits table.

The following statement is from the SQL Server database management system.


SELECT emp.empID, emp.empName
FROM employee emp
WHERE emp.employeeID IN
 (SELECT ben.empID
  FROM benefits ben
  WHERE ben.planCode = "Health"
 );


FROM

You can also place the subquery within the FROM clause of the SQL statement. Again, the goal is to filter our results to get a more manageable amount of information.

In the following example (also from SQL Server), we've created an alias for the subquery, called subquery1.

In this case, the alias does exactly as you'd think: It creates another name for the subquery. This actually very important because now we can refer to this query in other statements. We can even refer to the subquery from within the main query statement! SQL reads the entire statement.

The alias is important because it lets you reference that subquery, even from the main query. Notice how the SELECT statement even selects the count value from subquery1. If we didn't give it a name, it would not have worked.

The following shows how a subquery within a FROM statement would work:


SELECT emp.empID, emp.empName
FROM employee
 (SELECT planCode, COUNT(empID) as emp_count
  FROM benefits
  GROUP BY plancode) subquery 1
WHERE subquery1.empID = employee.empID;


Now let's pull out the subquery and look at it alone: The alias is defined OUTSIDE the subquery, after the closing parenthesis.


(SELECT planCode, COUNT(empID) as emp_count
FROM benefits
GROUP BY plancode) subquery 1


The alias name of subquery1 can now be used to refer to any fields from this subquery. In fact, if we look back at the first SELECT statement, we referenced this query:


SELECT empName, subquery1.emp_count


If we had not properly named the subquery, this statement would not have processed.

SELECT

Another location for a subquery is the SELECT statement. Having the subquery inside the SELECT statement is helpful when you want to complete some sort of an aggregate function as part of the subquery, and not the main query. That is, a count, summary, or minimum/maximum value. It could be the highest salary, the lowest product price, or count of employees.

Let's take a look at an example and then break it down. Again, this is from SQL Server.


SELECT o1.order_id, o1.order_code
 (SELECT MAX(order_amount)
  FROM orders o2
  WHERE o1.order_id = o2.order_id) subquery2
FROM orders o1;


Let's pull out the subquery and take a look:


SELECT MAX(order_amount)
FROM orders o2
  WHERE o1.order_id = o2.order_id) subquery2


Again, we have created an alias (this time naming it subquery2).

NOTE: In the SELECT statement, the subquery can only return ONE value. By using the Count feature, we will only get one value back, and that's the total count.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support