SQL Tutorial - Subqueries (Scalar Valued)
Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.
If you like the video you can support me on Patreon, / beardeddev
In this SQL Tutorial, I use the AdventureWorks2019 database, if you wish to follow along you can download the .bak file from: https://docs.microsoft.com/en-us/sql/...
This tutorial is all about subqueries, more specifically self-contained scalar value subqueries, self-contained subqueries mean that the inner query can be run independently unlike with correlated subqueries, scalar value subqueries must return one value and I demonstrate what happens in the video when multiple values are returned.
I talk through some examples of using subqueries within SELECT, we might use this to give context to the result set or a better use case is when we want to calculate a percentage of total.
Then it's on to using subqueries in WHERE to filter data, again I go through a typical example, show what happens when the subquery returns NULL and also the best place to start when we need to debug. We can also nest subqueries and I talk through an example of this, I also mention performance considerations when working with subqueries and it might be better to consider temporary objects depending on the situation.
In the last example I make a mistake and display the percentage incorrectly, I should have multiplied the results by 100.
To follow along you can use the code examples below:
SELECT
SalesOrderID,
OrderDate,
SalesOrderNumber,
PurchaseOrderNumber,
AccountNumber,
CustomerID,
TotalDue
FROM Sales.SalesOrderHeader
-- SELECT and WHERE example
SELECT
SalesOrderID,
OrderDate,
SalesOrderNumber,
PurchaseOrderNumber,
AccountNumber,
CustomerID,
TotalDue,
(SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader) AS LatestOrderDate,
100 * TotalDue / (SELECT SUM(TotalDue) FROM Sales.SalesOrderHeader) AS pctOfGrandTotal
FROM Sales.SalesOrderHeader
WHERE OrderDate = (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader);
-- nested subquery example
SELECT
SalesOrderID,
OrderDate,
SalesOrderNumber,
PurchaseOrderNumber,
AccountNumber,
CustomerID,
TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderID =
(
SELECT
MAX(SalesOrderID)
FROM Sales.SalesOrderHeader
WHERE OrderDate =
(
SELECT
MIN(OrderDate)
FROM Sales.SalesOrderHeader
)
)
-- expression example
SELECT
(
SELECT TOP(1)
SUM(TotalDue) AS Total
FROM Sales.SalesOrderHeader
GROUP BY
CustomerID
ORDER BY Total DESC
)
/
(
SELECT
SUM(TotalDue) AS GrandTotal
FROM Sales.SalesOrderHeader
)
AS PctOfGrandTotal;