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.

Read Also:  limit excerpt length in wordpress Example

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)

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
Read Also:  Top 5 Mobile Apps To Get Free PayTm Cash & Free Mobile Recharge

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.

Read Also:  sql subquery example

About Pakainfo

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.

View all posts by Pakainfo →

Leave a Reply

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