SQL: Subqueries
The easiest and simplest way to explain what a subquery is to say that it's a query within a query. For example if you want to get the employee that belongs to specific territory in the Northwind database without a join, you would have to use a subquery. Like the following subquery.
SELECT EmployeeID, (FirstName + ' ' + LastName) AS Name
FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID
FROM EmployeeTerritories
WHERE TerritoryID=01581)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYXmFq6qvsJyYfd97mqkMn9FI5liOETATE52d5XsKXXKyCa5mcZA-EhNiIg6xWVQ06Pov9AvIjsWK_EnzPjjbbth0QrR4ChopM5PZBkDM3Nyph42SFxLKqk4GLqPhh5syE_ITwt-j3yv4/s1600/subquery.png)
Things You Should Know About Subqueries:
- They are not the most efficient performance wise
- You can only retrieve a single column in the subquery, retrieving multiple columns will throw an error
SELECT CategoryName,
(SELECT AVG(UnitPrice)
FROM Products WHERE CategoryID = 1) AS AvgPrice
FROM Categories
WHERE CategoryID = 1
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdtyF-jzZSFJ8xZh0ts0-EICEby0WQAdVx-IWwP8piExRO0Iibgo6Wl1rgmGUAiNYPbymTKFqiZgxSI4G4o5gj_IZc7NREaamFxw2xQKOsAPL11FV-JnKXO5t34h66j3QkRj6Xd5-SJK8/s1600/subquery2.png)
Comments
Post a Comment