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.