WHERE and Subquery Combination in T-SQL using VB.NET

SQL WHERE clause is used to select data conditionally, lets learn how we can use subquery in that condition
  • 3847

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: 

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


More Articles

© 2020 DotNetHeaven. All rights reserved.