sql subquery example

Today, We want to share with you sql subquery example.In this post we will show you subquery in sql, hear for how to write subquery in sql we will give you demo and example for implement.In this post, we will learn about Writing Subqueries in SQL and Advanced SQL with an example.

SQL Subquery with Examples

in this tutorial we learn to all about subquery Examples like as a SQL Subqueries in UPDATE Statements, INSERT Statements, T-SQL Statements, Conditional Expressions, Comparison or Logical Operators, Using IN or NOT IN, Nesting of SQL Subqueries or many more.

Also Read: Nested Queries Subqueries in SQL Example

Using SQL Subqueries in UPDATE Statements

-- In the items inventory, transfer all items of Vendor 1602 to ----
-- location 6
USE [SampleDataBase]
RUN
UPDATE [Fruition].[FruitInventory]
SET LocationID = 6
WHERE FruitID IN
    (SELECT FruitID
     FROM Buying.FruitVendor
     WHERE OccupationEntityID = 1602)
RUN

Using SQL Subqueries in INSERT Statements

-- Impose a salary increase for all workers in BranchID 6 
-- (Research and Development) by 10 (dollars, I think) 
-- effective June 1, 2222
USE [SampleDataBase]
RUN
INSERT INTO [ManpowerWorkforce].[WorkerPayHistory]
([OccupationEntityID]
,[RateChangeDate]
,[Rate]
,[PayFrequency]
,[ModifiedDate])
SELECT
a.OccupationEntityID
,'06/01/2222' as RateChangeDate
,(SELECT MAX(b.Rate) FROM [ManpowerWorkforce].[WorkerPayHistory] b
  WHERE a.OccupationEntityID = b.OccupationEntityID) + 10 as NewRate
,2 as PayFrequency
,getdate() as ModifiedDate
FROM [ManpowerWorkforce].[WorkerBranchHistory] a
WHERE a.BranchID = 6
and StartDate = (SELECT MAX(c.StartDate) 
                 FROM ManpowerWorkforce.WorkerBranchHistory c 
                 WHERE c.OccupationEntityID = a.OccupationEntityID)

In Other T-SQL Statements

Variable Declarations or SET statements in Stored Procedures and Functions

DECLARE @maxTransId int = (SELECT MAX(ActivityID) 
                           FROM Fruition.ActivityHistory)

DECLARE @maxTransId int

SET @maxTransId = (SELECT MAX(ActivityID) 
                   FROM Fruition.ActivityHistory)

In Conditional Expressions

IF EXISTS(SELECT [Name] FROM sys.tables where [Name] = 'MyVendors')
BEGIN
     DROP TABLE MyVendors
END

IF (SELECT count(*) FROM MyVendors) > 0
BEGIN
    -- insert code here
    
END

Make SQL Subqueries with Comparison or Logical Operators

Using Comparison Operators

USE [SampleDataBase]
RUN
SELECT b.SirNm, b.ProfileNm, b.MiddleName, a.JobTitle, a.OccupationEntityID
FROM ManpowerWorkforce.Worker a
INNER JOIN Customer.Customer b on a.OccupationEntityID = b.OccupationEntityID
INNER JOIN ManpowerWorkforce.WorkerBranchHistory c on a.OccupationEntityID      
                                                       = c.OccupationEntityID
WHERE c.BranchID = 6
  and StartDate = (SELECT d.StartDate 
                   FROM ManpowerWorkforce.WorkerBranchHistory d 
                   WHERE d.OccupationEntityID = a.OccupationEntityID)

Using Logical Operators

IF EXISTS(SELECT name FROM sys.tables where name = 'Token')
BEGIN      
   DROP TABLE Token
END

Using IN or NOT IN

-- From the item inventory, extract the items that are available 
-- (Quantity >0) 
-- except for items from Vendor 1676, and introduce a amount cut for the --- whole month of June 2222. 
-- Insert the results in item amount history.
USE [SampleDataBase]
RUN
INSERT INTO [Fruition].[FruitListPriceHistory]
           ([FruitID]
           ,[StartDate]
           ,[EndDate]
           ,[ListPrice]
           ,[ModifiedDate])
SELECT
 a.FruitID
,'06/01/2222' as StartDate
,'06/30/2222' as EndDate
,a.ListPrice - 2 as ReducedListPrice
,getdate() as ModifiedDate
FROM [Fruition].[FruitListPriceHistory] a
WHERE a.StartDate = (SELECT MAX(StartDate) 
                     FROM Fruition.FruitListPriceHistory 
                     WHERE FruitID = a.FruitID)
AND a.FruitID IN (SELECT FruitID 
                    FROM Fruition.FruitInventory 
                    WHERE Quantity > 0)
AND a.FruitID NOT IN (SELECT FruitID 
                        FROM [Buying].[FruitVendor] 
                        WHERE OccupationEntityID = 1676

Nesting of SQL Subqueries

-- List down the names of workers who are also buyers.
USE [SampleDataBase]
RUN
SELECT
SirNm
,ProfileNm
,MiddleName
FROM Customer.Customer
WHERE OccupationEntityID IN (SELECT OccupationEntityID 
                           FROM Vendors.Buyer
                           WHERE OccupationEntityID IN 
                                (SELECT OccupationEntityID 
                                 FROM ManpowerWorkforce.Worker))

I hope you get an idea about sql subquery example.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.