Rule Definition
Having an SQL query inside a loop is usually the source of performance and scalability problems especially if the number of iterations become very high (for example if it is dependent on the data returned from the database). This iterative pattern has proved to be very dangerous for application performance and scalability. Database servers perform much better in set-oriented patterns rather than pure iterative ones.

Remediation
The remediation is often to replace the iterative approach based on a loop with a set-oriented one and thus modify the query.

Violation Code Sample

Oracle:
for x in ( select * from t1 )
loop
  for y in ( select * from t2 where t2.col = x.COL )
  loop 
    for z in (select * from t3 where t3.col = y.SOMETHING )
    loop
      update table_name set co1 = z.SOMETHING_ELSE where table_name.col2 = z.KeyName;
    end loop;
  end loop;
end loop;

Microsoft SQL Server:
WHILE @Counter <= @MaxOscars
BEGIN
SET @NumFilms =
(
SELECT COUNT(*)
FROM tblFilm
WHERE FilmOscarWins = @Counter
)
PRINT
CAST(@NumFilms AS VARCHAR(3)) +
' films have won ' +
CAST(@Counter AS VARCHAR(2)) +
' Oscars.'
SET @Counter += 1
END

Fixed Code Sample

Oracle:
 update table_name
 set co1 = (select z.SOMETHING_ELSE
              from t3 z
                  join t2 y on z.col = y.SOMETHING
                  join t1 x on y.col = x.COL
              where table_name.col2 = z.KeyName)
where exists(select 1
              from t3 z
                  join t2 y on z.col = y.SOMETHING
                  join t1 x on y.col = x.COL
              where table_name.col2 = z.KeyName);

Microsoft SQL Server:
SELECT
FilmOscarWins
,COUNT(*) AS [NumberOfFilms]
FROM
tblFilm
GROUP BY
FilmOscarWins

https://blogs.oracle.com/sql/post/avoid-writing-sql-inside-loops https://codeutopia.net/blog/2010/10/07/optimizing-sql-removing-queries-inside-loops/