Nested Loop Query when to use in SQL

Senn 41 Reputation points
2021-06-26T08:55:13.887+00:00

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!

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,607 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-06-26T09:56:24.7+00:00

    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;
    

2 additional answers

Sort by: Most helpful
  1. Senn 41 Reputation points
    2021-06-26T15:04:21.28+00:00

    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)


  2. Seeya Xi-MSFT 16,436 Reputation points
    2021-06-28T06:03:18.543+00:00

    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.

    0 comments No comments