Hello and welcome to the fourth part of our SQL Practice Set in MS SQL Server! Today we'll work with subqueries! We'll start with simple, uncorrelated subqueries. (We'll revisit correlated subqueries later in this part.) Here's a brief reminder:
A subquery is a query within another query.
We can use subqueries in the
WHERE clause to compare a given column with the result of a whole query. When comparing with the result of the subquery, you can use comparison operators by themselves:
WHERE Age > (SELECT Age FROM Cats WHERE CatName = 'Kitty')
or comparison operators with the
ALL keywords, if your subquery can return multiple rows:
WHERE Age > ANY (SELECT Age FROM Cats WHERE CatName = 'Kitty')
or the operator
IN, if the value of the column compared with the subquery has to be in the result of particular subquery, e.g.
WHERE Age IN (SELECT Age FROM Cats WHERE CatName LIKE 'K%')
We can also use the subqueries in the
FROM clause, and filter our rows in this way. The subquery in the
FROM clause has to have an alias.
(SELECT Breed, COUNT(*) AS NumberOfCats
GROUP BY Breed) BreedCount