Posted inMysql / Mysqli / php / Programming / Technology

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

,(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]
SELECT BuyerID, VendorsOrderID
FROM Vendors.VendorsOrderHeader
                     FROM [SampleDataBase].[Vendors].[Buyer]
                     WHERE TerritoryID = 4)


USE [SampleDataBase]
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]
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]
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]
SELECT [ShipYear],
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 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 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