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:  sql multiple subqueries in (select statement)

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:  Dynamically Add Input Fields And Submit To Database With jQuery and PHP

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

Related FAQ

Here are some more FAQ related to this Article:

  1. Read Also:  Laravel Clear Cache Using Artisan Command (CLI)
  2. Read Also:  jQuery get value of selected radio button
  3. Read Also:  Check and Delete Image from Public folder in Laravel
  4. Read Also:  Laravel Redirect To Another URL / Web Page - HTTP Laravel Redirects
  5. Read Also:  sql subquery multiple tables Example
  6. Read Also:  SQL SELECT DISTINCT Statement
  7. Read Also:  Laravel 5.6 Share Variable With Display Data in View
  8. Read Also:  how to write subquery in sql
  9. Read Also:  PHP Laravel 6 Flash Message Notification
  10. Read Also:  SQL Subquery, IN SELECT - with Examples