How to Querying Data by using Subqueries in SQL Programming: Part 1

February 04, 2014 , , , 0 Comments

In SQL programming, while querying data from multiple tables, you might need to use the result of one query as an input for the condition of another query. For example, in the Adventure Works database, you need to view the designation of all the employees who earn more than the average salary. In such cases, you can use subqueries to assign values to the expressions in other queries.

A subquery is an SQL statement that is used within another SQL statement. Subqueries are nested inside the WHERE or HAVING clause of the SELECT, INSERT, UPDATE, and DELETE statements. The query that represents the parent query is called an outer query, and the query that represents the subquery is called an inner query. The database engine executes the inner query first and returns the result to the outer query to calculate the result set.

Depending on the output generated by the subquery and the purpose for which it is to be used in the outer query, you can use different keywords, operators, and functions in subqueries.

Using the IN and EXISTS keywords

A subquery returns values that are used by the outer query. A subquery can return one or more values. Depending on the requirement, you can use these values in different ways in the outer query.

For example, in the Adventure Works database, you need to display the department name for an employee whose BusinessEntityID is 46. To perform this task, you can use the following query:

SELECT Name FROM HumanResources.Department
WHERE DepartmentID =
(SELECT DepartmentID FROM HumanResources.EmployeeDepartmentHistory WHERE BusinessEntityID = 46 AND EndDate IS NULL)

In the preceding query, the inner subquery returns the DepartmentID column of the emoloyee with BusinessEntityID as 46. Using this DepartmentID, the outer query returns the name of the department from the Department table i.e. Production.

In this query, EndDate is NULL Signifying that you need to extract the ID of the department where the emoloyee is currently working.

In the preceding example, the subquery returns a single value. However, at times, you need to return more than one value from the subquery. In addition, you might need to use a subquery only to check the existence of some records and based on that you need to execute the outer query.

You can specify different kinds of conditions on subqueries by using the following keywords:

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google