sql subquery examples

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  (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)

Example

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

Single Value

-- 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)

Multiple Values

-- 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.