Nested Loop Query when to use in SQL

Question

Hi everyone,

I am not so familiar with nested loop query. What kind of question should I use the nested loop query or what is the hint for it? Once It looks for youngest but I don't use a nested query but for the second one I am using a nested query. Why? When do I have to use Having Count?

Find the age of the youngest sailor for each rating level.

SELECT MIN(S.age)
FROM Sailor S
GROUP BY S.rating

Find the age of the youngest sailor for each rating level.

SELECT S.sname
FROM Sailor S
WHERE S.age = (SELECT MAX(S2.age) FROM Sailor S)

Thanks!

Answer 1

Here is an example of a JOIN query:

SELECT O.OrderID, O.OrderDate, C.CustomerName, O.TotalAmount
FROM   Orders O
JOIN    Customers C ON O.CustomerID = C.CustomerID
WHERE  C.City = 'Stockholm';

This query lists some information about orders placed by customers in Stockholm.

When computing the query, SQL Server can implement the join in the query in different ways, and one of them is a Nested Loops Join. But that is not really your business. SQL is a declarative language: You say what result you want, and the optimizer figures out how to compute it.

To find the name of the youngest sailor per rating, this can be expressed in more than one way, but here is one pattern.

WITH numbering AS (
    SELECT S.Name, S.Age, S.Rating rank = rank() OVER(PARTITION BY S.Rating ORDER BY S.Age)
    FROM Sailor S
)
SELECT n.Name, n.Age, n.Rating
FROM  numbering
WHERE  rank = 1;

Answer 2

For example for this problem:

Find the names of faculty members who teach in every room in which some course is taught. Here I dont see the solution. Can you explain why should I do twice a Count?

The solution is following:

SELECT DISTINCT F.fname
FROM Faculty F
WHERE (SELECT COUNT(distinct C.room)
FROM Course C) =
(SELECT COUNT(distinct C1.room)
FROM Course C1
WHERE C1.fid = F.fid)

Answer 3

Hi @Senn ,

If the amount of data to be queried is large, you can use multiple queries or use temporary tables to store intermediate results, etc. to avoid using nested queries that affect performance.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.