What are Subqueries and What Do They Do?
Before moving on to examples for subquery, let’s explain what subquery is and what it does. In our previous article, we explained bookmarks in Power Bi business intelligence software.
In SQL, subqueries are another query within a query and are used to provide intermediate results to the main query. Subqueries make database queries more flexible and powerful
Usage Areas of Subqueries :
- Filtering: Subqueries filter the data set of the main query according to a specific criterion. For example, it is used to pull data that is greater than a certain value.
- Totals and Averages: Can be included in the main query by calculating totals or averages for a given set.
- Data Transformation: Used to convert datasets into a different form. For example, to list orders in a specific date range.
- Nested Queries: Nested sub-queries can be used to perform more complex analysis. For example, finding the highest paid employees.
- Dependent and Independent Subqueries: Independent subqueries work independently of the main query, while dependent subqueries work depending on the values in the main query.
-
- Advantages of Subqueries
- Managing Complexity: Breaks complex queries into smaller, more manageable pieces.
- Modularity: Increases the flexibility of queries by creating reusable query fragments.
- Data Hiding: Improves performance and ensures data security by hiding unnecessary data.
Here are our Subquery Examples: These examples are prepared with Northwind database. You can download Northwind database from the link below
https://github.com/cjlee/northwind
1-List the category name of the product with the highest unit price
select top 1 c.CategoryName,
(select max(p.UnitPrice) from products p where p.CategoryID=c.CategoryID)
from categories c
Output :
2- List prices of products with category Beverages
select p.ProductName,p.UnitPrice from products p
where p.CategoryID = (
select c.CategoryID from Categories c where c.CategoryName='Beverages'
)
Output :
3- List customers with no orders
select*from Customers c where CustomerID not in (
select distinct CustomerID from orders)Output :
4-List the customers who did not have an order in 1996
select c.CompanyName from Customers c where not EXISTS (
select distinct o.CustomerID from orders o where DATEPART(YEAR,o.OrderDate)=1996 and o.CustomerID=c.CustomerID)Output :
5- Products with type CategoryName=’Beverages’ with higher than average unit price
select*from products p
where p.CategoryID = (
select c.CategoryID from Categories c where c.CategoryName='Beverages'
)
and p.UnitPrice >(
select avg(p1.UnitPrice) from Products p1
)Output :
6- Bring the total order amounts and quantities together with the names of the customers by calculating them from the order tables
SELECT
c.CustomerID,
c.CompanyName,
(SELECT SUM(od.Quantity)
FROM Orders o
LEFT JOIN [Order Details] od ON od.OrderID = o.OrderID
WHERE o.CustomerID = c.CustomerID) AS Amount_of_Orders,
(SELECT SUM(od.Quantity * od.UnitPrice)
FROM Orders o
LEFT JOIN [Order Details] od ON od.OrderID = o.OrderID
WHERE o.CustomerID = c.CustomerID) AS Total_of_Orders
FROM Customers c;
Output :
7- List the number of orders received by each employee and the total amount of these orders
SELECT
e.EmployeeID,
e.FirstName,
e.LastName,
(SELECT COUNT(*)
FROM Orders o
WHERE o.EmployeeID = e.EmployeeID) AS Order_Count,
(SELECT SUM(od.Quantity * od.UnitPrice)
FROM Orders o
LEFT JOIN [Order Details] od ON od.OrderID = o.OrderID
WHERE o.EmployeeID = e.EmployeeID) AS Total_Order_Amount
FROM Employees e;
Output :
8- Find the customer with the most orders
SELECT
c.CustomerID,
c.CompanyName,
(SELECT COUNT(*) FROM Orders o WHERE o.CustomerID = c.CustomerID) AS Total_Orders FROM Customers c WHERE c.CustomerID = ( SELECT TOP 1 CustomerID FROM Orders GROUP BY CustomerID ORDER BY COUNT(*) DESC
);
Output :
9- Find the most ordered product
SELECT
p.ProductID,
p.ProductName,
(SELECT SUM(od.Quantity)
FROM [Order Details] od
WHERE od.ProductID = p.ProductID) AS Total_Quantity
FROM Products p
WHERE p.ProductID = (
SELECT TOP 1 od.ProductID
FROM [Order Details] od
GROUP BY od.ProductID
ORDER BY SUM(od.Quantity) DESC
);
Output :
10- List the average price and total stock of products in each category
SELECT
c.CategoryID,
c.CategoryName,
(SELECT AVG(p.UnitPrice)
FROM Products p
WHERE p.CategoryID = c.CategoryID) AS Average_Price,
(SELECT SUM(p.UnitsInStock)
FROM Products p
WHERE p.CategoryID = c.CategoryID) AS Total_Stock
FROM Categories c;
Output :
AHMET GÜNOĞLU
ERP Consultant and Business Intelligence Expert