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.
There are two kind of subquery in SQL one is called non-correlated and other is called correlated subquery.
In non correlated subquery, inner query doesn't depend on outer query and can run as stand alone query.
NonCorrelated Subquery Example:
SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE = (SELECT RIC FROM Market WHERE COUNTRY='Japan');
SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE IN (SELECT RIC FROM Market WHERE COUNTRY='United States' OR COUNTRY= 'INDIA');
Correlated SubQuery in SQL
Correlated subqueries are the one in which inner query or subquery reference outer query. Outer query needs to be executed before inner query.
correlated subqueries are slower queries and one should avoid it as much as possible.
SELECT m.NAME FROM Market m WHERE m.RIC = (SELECT s.LISTED_ON_EXCHANGE FROM Stock s WHERE s.LISTED_ON_EXCHANGE=m.RIC);
Difference between Correlated and NonCorrelated Subquery
1.In case of correlated subquery inner query depends on outer query while in case of noncorrelated query inner query or subquery doesn't depends on outer query and run by its own.
2.In case of correlated subquery, outer query executed before inner query or subquery while in case of NonCorrelated subquery inner query executes before outer query.
3.Correlated Sub-queries are slower than non correlated subquery and should be avoided in favor of sql joins.
4.Common example of correlated subquery is using exits and not exists keyword while non correlated query mostly use IN or NOT IN keywords.
In terms of performance SQL Joins are more efficient than subqueries.
Read full article from SubQuery Example in SQL – Correlated vs Noncorrelated
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.
There are two kind of subquery in SQL one is called non-correlated and other is called correlated subquery.
In non correlated subquery, inner query doesn't depend on outer query and can run as stand alone query.
NonCorrelated Subquery Example:
SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE = (SELECT RIC FROM Market WHERE COUNTRY='Japan');
SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE IN (SELECT RIC FROM Market WHERE COUNTRY='United States' OR COUNTRY= 'INDIA');
Correlated SubQuery in SQL
Correlated subqueries are the one in which inner query or subquery reference outer query. Outer query needs to be executed before inner query.
correlated subqueries are slower queries and one should avoid it as much as possible.
SELECT m.NAME FROM Market m WHERE m.RIC = (SELECT s.LISTED_ON_EXCHANGE FROM Stock s WHERE s.LISTED_ON_EXCHANGE=m.RIC);
Difference between Correlated and NonCorrelated Subquery
1.In case of correlated subquery inner query depends on outer query while in case of noncorrelated query inner query or subquery doesn't depends on outer query and run by its own.
2.In case of correlated subquery, outer query executed before inner query or subquery while in case of NonCorrelated subquery inner query executes before outer query.
3.Correlated Sub-queries are slower than non correlated subquery and should be avoided in favor of sql joins.
4.Common example of correlated subquery is using exits and not exists keyword while non correlated query mostly use IN or NOT IN keywords.
In terms of performance SQL Joins are more efficient than subqueries.
Read full article from SubQuery Example in SQL – Correlated vs Noncorrelated
