Explain subquery and corelated subquery

Question

Accepted Answer

Subquery is a query within another query. Correlated subquery is a subquery that depends on the outer query.

Subquery is used to retrieve data that will be used in the main query as a condition or to perform calculations.
Correlated subquery is used to retrieve data from a table based on values from another table in the outer query.
Subquery is executed first before the main query while correlated subquery is executed for each row of the outer query.
Example of subquery: SELECT * FROM table1 WHERE column1 IN (SELECT column2 FROM table2)
Example of correlated subquery: SELECT * FROM table1 t1 WHERE column1 = (SELECT MAX(column2) FROM table2 t2 WHERE t2.column3 = t1.column3)

Top Elait It Technologies Services Software Tester interview questions & answers