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.