In this tutorial you will learn how to create a self-contained multi-valued subquery. You may be wondering, what exactly is a multi-valued subquery? It is a subquery that returns multiple values as a single column, regardless of whether the subquery is self-contained. It is used in a WHERE or HAVING expression that contains IN or a comparison operator that is modified by ANY or ALL. The IN predicate operates on a multi-valued subquery and the basic form looks like this: <scalar_expression> IN (<multi-valued subquery>).

Setting Up

In this tutorial we will use two tables, Employees and Orders. Both tables have different columns but have a similar one in ‘EmpId’. This will serve as a way to connect the two tables when writing queries. Insert the values as shown in the screen shots below into the tables after creating them.

Employees

– EmpId (int, not null), FirstName (nvarchar(30), not null), LastName (nvarchar(30), not null), HireDate (datetime, not null), MgrId (int, null), Ssn (nvarchar(20), not null), Salary (money, not null).

Orders

– OrderId (PK, int, not null), EmpId (int, not null), CustId (nvarchar(30), not null), OrderTs (datetime, not null), Qty (int, not null).

Multi-Valued Subquery

In this query we will select data from a table and filter it to find specific data. To do this we will use the WHERE clause twice, first to select EmpId from the Orders table and second to select LastName in the Employees table.

As you can see from the code above, we use the typical SELECT, FROM, and WHERE statements/clause, but notice that we use them again after the IN operator. Remember that the IN operator allows you to specify multiple values in a WHERE clause thus allowing us to select multiple values in the LastName column.

Output

Since we specified to look for last names that begin with letters ‘D’ or ‘J’, the query returned orders of employees whose last name begin with ‘D’ or ‘J’. In this case we have two employees whose last name begin with those letters, John Johnson and Nathan Drake. Johnson has an EmpId of 1 and Drake has an EmpId of 6. If you look in the Orders table, we can see orders made by EmpId 1 and 6. The OrderId of those orders are 2, 3, 13, and 14, the same as what we got in the output.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Download Source Files