Today, We want to share with you sql subquery examples.In this post we will show you subquery mysql, hear for Using Subqueries in the Select Statement we will give you demo and example for implement.In this post, we will learn about The Ultimate Guide To SQL Server Subquery with an example.
sql multiple subqueries in (select statement)
in this tutorial we learn to all about subquery Examples like as a SQL Subqueries, Self-Contained or Correlated, Single Value, Multiple values, Whole tables or many more.
Also Read: Nested Queries Subqueries in SQL Example
SQL Subqueries Syntax
SELECT col1 ,col2 ,(subquery) as col3 FROM table1 [JOIN table2 ON table1.col1 = table2.col2] WHERE col1 <operator> (subquery)
Self-Contained or Correlated
-- Get vendors books of buyers from Southwest United States -- (TerritoryID = 4) USE [SampleDataBase] RUN SELECT BuyerID, VendorsOrderID FROM Vendors.VendorsOrderHeader WHERE BuyerID IN (SELECT [BuyerID] FROM [SampleDataBase].[Vendors].[Buyer] WHERE TerritoryID = 4)
USE [SampleDataBase] RUN SELECT DISTINCT a.SirNm, a.ProfileNm, b.OccupationEntityID FROM Customer.Customer AS p JOIN ManpowerWorkforce.Worker AS e ON p.OccupationEntityID = e.OccupationEntityID WHERE 1262000.00 IN (SELECT [VendorsQuota] FROM Vendors.VendorsCustomerQuotaHistory spq WHERE p.OccupationEntityID = spq.OccupationEntityID)
Make SQL Subqueries With 3 Possible Returned Values
In fact, there are 3 possible outcomes:
- A single value
- Multiple values
- Whole tables
-- Output a single value which is the maximum or last ActivityID USE [SampleDataBase] RUN SELECT ActivityID, FruitID, ActivityDate, Quantity FROM Fruition.ActivityHistory WHERE ActivityID = (SELECT MAX(t.ActivityID) FROM Fruition.ActivityHistory t)
-- Output multiple values which is a list of buyers with lastnames that --- start with 'I' 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.lastname LIKE N'I%' AND p.CustomerType='SC')
Whole Table Values
-- Output a table of values based on vendors books USE [SampleDataBase] RUN SELECT [ShipYear], COUNT(DISTINCT [BuyerID]) AS BuyerCount FROM (SELECT YEAR([ShipDate]) AS [ShipYear], [BuyerID] FROM Vendors.VendorsOrderHeader) AS Shipments GROUP BY [ShipYear] ORDER BY [ShipYear]
I hope you get an idea about sql subquery examples.
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.
Here are some more FAQ related to this Article: