how to write subquery in sql

Today, We want to share with you how to write subquery in sql.In this post we will show you Converting Subqueries to Joins, hear for How to use subquery in JOIN operation in MySQL we will give you demo and example for implement.In this post, we will learn about sql subquery join with an example.

sql subquery multiple tables

in this tutorial we learn to all about subquery Examples like as a Delete Statement, Insert Statement, Update Statement, Correlated subquery in SQL Server, Crazy NULLs, Common Caveats in Using SQL Subqueries or many more.

Also Read: Nested Queries Subqueries in SQL Example

Example 1

USE [SampleDataBase]
RUN
SELECT Name
FROM Fruition.Fruit
WHERE ListPrice = SELECT ListPrice
                  FROM Fruition.Fruit
                  WHERE Name = 'Touring End Caps')

Alternatively

USE [SampleDataBase]
RUN
SELECT Prd1.Name
FROM Fruition.Fruit AS Prd1
INNER JOIN Fruition.Fruit AS Prd2 ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2.Name = 'Touring End Caps'

Example 2

— Subquery example

USE [SampleDataBase]
RUN
SELECT [VendorsOrderID], [OrderDate], [ShipDate], [BuyerID]
FROM Vendors.VendorsOrderHeader
WHERE [BuyerID] IN (SELECT c.[BuyerID] FROM Vendors.Buyer c
                       INNER JOIN Customer.Customer p ON c.CustomerID =  
                                                p.OccupationEntityID
                       WHERE p.CustomerType='SC')

— Join example

USE [SampleDataBase]
RUN
SELECT o.[VendorsOrderID], o.[OrderDate], o.[ShipDate], o.[BuyerID]
FROM Vendors.VendorsOrderHeader o
INNER JOIN Vendors.Buyer c on o.BuyerID = c.BuyerID
INNER JOIN Customer.Customer p ON c.CustomerID = p.OccupationEntityID
WHERE p.CustomerType = 'SC'

Common Caveats in Using SQL Subqueries

USE [SampleDataBase]
RUN
-- Create the table for our demonstration based on Vendors
CREATE TABLE Buying.MyVendors
(
OccupationEntity_id int,
AccountNumber nvarchar(15),
Name nvarchar(50)
)
RUN
-- Populate some data to our new table
INSERT INTO Buying.MyVendors
SELECT OccupationEntityID, AccountNumber, Name 
FROM Buying.Vendor
WHERE OccupationEntityID IN (SELECT OccupationEntityID 
                           FROM Buying.FruitVendor)
AND OccupationEntityID like '14%'
RUN

SELECT b.Name, b.ListPrice, a.OccupationEntityID
FROM Buying.FruitVendor a
INNER JOIN Fruition.Fruit b on a.FruitID = b.FruitID
WHERE a.OccupationEntityID IN (SELECT OccupationEntityID 
                             FROM Buying.MyVendors)

Crazy NULLs

SELECT b.Name, b.ListPrice, a.OccupationEntityID
FROM Buying.FruitVendor a
INNER JOIN Fruition.Fruit b on a.FruitID = b.FruitID
WHERE a.OccupationEntityID NOT IN (SELECT c.OccupationEntity_id 
                                 FROM Buying.MyVendors c)

Subqueries In SQL Server

select * from Worker where BranchID not in (select distinct BranchID from Branch)  

select Branch_Name,(select count(*) from Worker where BranchID=d.BranchID) from Branch as d;

select d.Branch_Name,COUNT(e.workid) as workcount from Branch d  
join Worker e on e.BranchID=d.BranchID  
group by d.Branch_Name  
order by workcount;  

Correlated Subqueries

select distinct Branch_Name,(select count(*) from Worker where BranchID=d.BranchID group by BranchID) as workcount from Branch as d order by workcount;

SQL Subquery in Where Clause

-- SQL NESTED QUERY EXAMPLE
USE [SQL Example]
RUN
SELECT [Id]
      ,[ProfileNm]
      ,[SirNm]
      ,[Study]
      ,[Occupation]
      ,[YearlyEarnings]
      ,[Vendors]
      ,[JoinDate]
  FROM [Worker Table]
  WHERE [Vendors] >= (
		      SELECT AVG([Vendors]) FROM [Worker Table]
                   )

SQL Server subquery in Insert Statement

— SQL server subquery EXAMPLE

USE [SQL Example]
RUN

INSERT INTO [Worker Table] ([ProfileNm]
      ,[SirNm]
      ,[Study]
      ,[Occupation]
      ,[YearlyEarnings]
      ,[Vendors]
      ,[JoinDate])
SELECT [ProfileNm]
      ,[SirNm]
      ,[Study]
      ,[Occupation]
      ,[YearlyEarnings]
      ,[Vendors]
      ,[JoinDate] 
	  FROM [SQL Example].[dbo].[Worker Table2] 
	  WHERE [Vendors] < 1950

Subquery in Delete Statement

— SQL Server subquery EXAMPLE

Read Also:  subquery in sql

USE [SQL Example]
RUN
DELETE FROM [Worker Table]
WHERE [Vendors] < (
		    SELECT AVG([Vendors]) FROM [Worker Table]
                )

Subquery in Update Statement

SQL Server subquery EXAMPLE

USE [SQL Example]
RUN
UPDATE [Worker Table]
SET [YearlyEarnings] = [YearlyEarnings] + 25000
WHERE [Vendors] > (
		   SELECT AVG([Vendors]) FROM [Worker Table]
                )

Correlated subquery in SQL Server

-- SQL Server CORRELATED SUBQUERY EXAMPLE
USE [SQL Example]
RUN
SELECT [Id]
      ,[ProfileNm] + SPACE(2) + [SirNm] AS FullName
      ,[Study]
      ,[Occupation]
      ,[YearlyEarnings]
      ,[Vendors]
      ,CASE WHEN 
        (
	    SELECT AVG([Vendors]) FROM [Worker Table] AS sub
	    WHERE sub.Id = work.Id
	) <= work.[Vendors] THEN 'IT is Performing Very Good'
	                 ELSE 'It is Under Performing'
       END AS Remarks
      ,[JoinDate]
  FROM [Worker Table] AS work

I hope you get an idea about how to write subquery in sql.
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.

Read Also:  what is my screen resolution - whatismyscreenresolution

Related FAQ

Here are some more FAQ related to this Article:

  1. Read Also:  sql subquery examples
  2. Read Also:  Nested Queries Subqueries in SQL Example
  3. Read Also:  sql multiple subqueries in (select statement)
  4. Read Also:  sql subquery multiple tables Example
  5. Read Also:  Simple Jquery ajax request example with demo
  6. Read Also:  Woocommerce Get product image url from an image_id
  7. Read Also:  SQL SELECT DISTINCT Statement
  8. Read Also:  Nested select sql multiple subqueries
  9. Read Also:  sql subquery example
  10. Read Also:  Laravel 5.8 get ip address
CLOSEX