SQL SERVER – Correlated and Noncorrelated – SubQuery

SQL SERVER – Correlated and Noncorrelated – SubQuery Introduction, Explanation and Example

Sub Query - SubQuery in SQL is a query inside another query.
subQuery is a way to combine or join them in single query. SQL query which is on inner part of main query is called inner query while outer part of main query is called outer query. for example in below sql query section not highlighted is OUTER query while section highlighted with grey is INNER query.

SELECT name FROM City WHERE pincode IN(SELECT pincode FROM pin WHERE zone='west')

SubQuery Rules in SQL

Like any other concept in SQL, subquery also has some rules and you can only embed one query inside another by following rules :

1. subquery can be used in insert statement.

2. subquery can be used in select statement as column.

3. subquery should always return either a scaler value if used with where clause or value from a column if used with IN or NOT IN clause.

Correlated SubQuery - Correlated Subquery is a sub-query that uses values from the outer query or Correlated subqueries are the one in which inner query or subquery reference outer query. Outer query needs to be executed before inner query.

NonCorrelated SubQuery - In non correlated subquery, inner query doesn't depend on outer query and can run as stand alone query.

http://blog.sqlauthority.com/2007/09/07/sql-server-correlated-and-noncorrelated-subquery-introduction-explanation-and-example/

http://www.programmerinterview.com/index.php/database-sql/correlated-vs-uncorrelated-subquery/

http://beginner-sql-tutorial.com/sql-subquery.htm