Posted inMysql / Mysqli / php / Programming / Technology

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


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.

I am Jaydeep Gondaliya , a software engineer, the founder and the person running Pakainfo. I'm a full-stack developer, entrepreneur and owner of Pakainfo.com. I live in India and I love to write tutorials and tips that can help to other artisan, a Passionate Blogger, who love to share the informative content on PHP, JavaScript, jQuery, Laravel, CodeIgniter, VueJS, AngularJS and Bootstrap from the early stage.

Leave a Reply

Your email address will not be published. Required fields are marked *

We accept paid guest Posting on our Site : Guest Post Chat with Us On Skype