In this tutorial you will learn what a self-contained scalar subquery is and how to create one. A scalar subquery is a subquery that returns a single value regardless if it is self-contained. These subqueries can appear anywhere in the outer query where a single-valued expression can appear, such as the WHERE and SELECT clauses. In this example we will select specific orders from an Orders table with a last name beginning with “B” in the Employees table and output them.
Before we start working the self-contained scalar subquery, we need to create two tables called Employees and Orders. Table Employees will have seven columns: EmpId, FirstName, LastName, HireDate, MgrId, Ssn, and Salary. Table Orders will have five columns: OrderId, EmpId, CustId, OrderTs, and Qty.
Employees Table Orders Table
Enter the data show in the screenshots below into the appropriate table:
Self-Contained Scalar Subquery
Now that we created an Employees and Orders table, as well as add data into both tables, we can now begin writing a query to look for ‘OrderIds’ with a ‘LastName’ that begins with the letter “B”. To do this we use the SELECT statement to select a table and then use the FROM statement to select a column.
SELECT OrderId FROM Orders
In the same query we will specify to select ‘EmpIds’ where the ‘LastName’ of the employee begins with the letter “B”. We will use the WHERE clause for this, which is used to filter results. First we specify that we want EmpId in the opening WHERE clause statement. We the use the “All” subquery to select all instances of the proceeding statement that will select the ‘EmpId’ from the Employees table where the ‘LastName’ begins with the letter “B”.
WHERE EmpId = ALL (SELECT EmpId FROM Employees WHERE LastName LIKE N‘B%’);
Execute the query and it should successfully execute. You will see two results, OrderId’s 6 and 8.
If you look back at the Employees table data, there is one last name that begins with B, “Blue”. “Ryu Blue” has an EmpId of 3 and if you look at the Orders table, two orders were made with an EmpId of 3, orders 6 and 8. Instead of going back and forth between tables, writing a query that eliminates the hassle is much more efficient.
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.