The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query you can also say it a select statement specifies the search conditions that determine which rows are retrieved. Subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a result set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. It is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.
Let see the syntax of using the subquery within the where clause:
Let suppose we have two tables one is worker.
Worker_ID Worker_Name Joining_Date Salary City Designation
------------ ---------------- ---------------- ---------- ------ ---------------
01 Jason 25-JUL-06 10000 NY TL
02 Black 21-MAR-76 6000 Torr. Executive
03 Mathews 15-MAR-90 8000 Torr. Executive
04 Green 24-OCT-82 5000 Torr. Manager
05 Albert 08-AUG-98 9500 Torr. Executive
06 Roma 30-JUL-87 7000 Van. Executive
Second table is Admin.
Worker_ID Worker_Name job_profile Manager_ID
------------ ---------------- ---------------- -------------
01 Jason Team Leader 20
02 Black Exwcutive 21
03 Mathews Advisor 22
04 Green Fitter 23
05 Albert Cutter 24
06 Roma Fore Man 25
Example of subquery within the where clause:
1>
2>
3>
4> UPDATE worker
5> SET Worker_Name = "manish"
6> WHERE Worker_ID = 02
7> (SELECT Worker_ID
8> FROM Admin
9> WHERE Manager_ID = 22)
10> GO