SubQuery inwards SQL is a query within roughly other query. Some fourth dimension to larn a item information from database you lot may postulate to burn 2 carve upward sql queries, subQuery is a means to combine or bring together them inwards unmarried query. SQL query which is on inner business office of principal query is called inner query acre outer business office of principal query is called outer query. for illustration inwards below sql query
SELECT mention FROM City WHERE pincode IN (SELECT pincode FROM pivot WHERE zone='west')
section non highlighted is OUTER query acre department highlighted alongside gray is INNER query. In this SQL tutorial nosotros volition run into both Correlated in addition to non correlated sub-query in addition to at that topographic point examples, roughly differences betwixt correlated in addition to noncorrelated subqueries in addition to in conclusion subquery vs join which is classic debatable theme inwards SQL. By the means this SQL tutorial is side past times side inwards serial of SQL in addition to database articles inwards similar truncate vs delete in addition to 10 examples of SELECT queries. If you lot are novel hither therefore you lot may honor those examples interesting.
SubQuery Rules inwards SQL
Like whatever other concept inwards SQL, subquery too has roughly rules in addition to you lot tin exclusively embed i query within roughly other past times next rules :
1. subquery tin endure used inwards insert statement.
2. subquery tin endure used inwards choose contestation every bit column.
3. subquery should e'er render either a scaler value if used alongside where clause or value from a column if used alongside IN or NOT IN clause.
Before going to sympathise non-correlated in addition to correlated subquery, let’s run into the tabular array in addition to information which nosotros are going to purpose inwards this example. Until you lot bring an agreement of how tabular array expect similar in addition to what form of information it stores its niggling hard to sympathise queries. In this subquery illustration nosotros volition purpose 2 tabular array Stock in addition to Market. Stock holds dissimilar stocks in addition to Market holds all stock exchanges inwards the world.
mysql> choose * from stock;
+---------+-------------------------+--------------------+
| RIC | COMPANY | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T | Sony | T |
| GOOG.O | Google Inc | O |
| GS.N | Goldman Sachs Group Inc | N |
| INDIGO | INDIGO Airlines | NULL |
| INFY.BO | InfoSys | BO |
| VOD.L | Vodafone Group PLC | L |
+---------+-------------------------+--------------------+
6 rows inwards laid upward (0.00 sec)
mysql> select from Market;
+------+-------------------------+---------------+
| RIC | NAME | COUNTRY |
+------+-------------------------+---------------+
| T | Tokyo Stock Exchange | Japan |
| O | NASDAQ | the States |
| N | New York Stock Exchange | United States |
| BO | Mumbai Stock Exchange | India |
+------+-------------------------+---------------+
4 rows inwards laid upward (0.00 sec)
Noncorrelated subquery inwards SQL
There are 2 form of subquery inwards SQL i is called non-correlated in addition to other is called correlated subquery. In non correlated subquery, inner query doesn't depend on outer query in addition to tin run every bit stand upward lone query.Subquery used along-with IN or NOT IN sql clause is expert examples of Noncorrelated subquery inwards SQL. Let's a noncorrelated subquery example to sympathise it better.
NonCorrelated Subquery Example:
Let’s run into the query “Find all stocks from Japan”, If nosotros analyze this query nosotros know that stock names are stored inwards Stock tabular array acre Country name is stored inwards Market table, therefore nosotros postulate to burn 2 query commencement to larn RIC for Japanese marketplace in addition to than all stocks which is listed on that Market. nosotros tin combine these 2 queries into i sql query past times using subquery every bit shown inwards below example: mysql> SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE = (SELECT RIC FROM Market WHERE COUNTRY='Japan');
+---------+
| COMPANY |
+---------+
| Sony |
+---------+
1 row IN SET (0.02 sec)
+---------+
| COMPANY |
+---------+
| Sony |
+---------+
1 row IN SET (0.02 sec)
Here business office which is within bracket is called inner query or subquery. As you lot run into inwards this illustration of subquery, inner query tin run lone and its non depended on outer query in addition to that's why its called NonCorrelated query.
NonCorrelated Subquery Example alongside IN Clause SQL
NonCorrelated subquery are used along-with IN in addition to NOT IN clause. hither is an illustration of subquery alongside IN clause inwards SQL.
SQL query: Find all stocks from the States in addition to India
mysql> SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE IN (SELECT RIC FROM Market WHERE COUNTRY='United States' OR COUNTRY= 'INDIA');
+-------------------------+
| COMPANY |
+-------------------------+
| Google Inc |
| Goldman Sachs GROUP Inc |
| InfoSys |
+-------------------------+
+-------------------------+
| COMPANY |
+-------------------------+
| Google Inc |
| Goldman Sachs GROUP Inc |
| InfoSys |
+-------------------------+
When Subquery is used along-with IN or NOT IN Clause it returns effect from i column instead of Scaler value.
Correlated SubQuery inwards SQL
Correlated subqueries are the i inwards which inner query or subquery reference outer query. Outer query needs to endure executed earlier inner query. One of the most mutual example of correlated subquery is using keywords exits in addition to not exits. An of import signal to banker's complaint is that correlated subqueries are slower queries in addition to i should avoid it every bit much every bit possible.
Example of Correlated Subquery inwards SQL
Here is an illustration of Correlated subquery “Return all markets which has at to the lowest degree i stock listed on it.”
mysql> SELECT m.NAME FROM Market thousand WHERE m.RIC = (SELECT s.LISTED_ON_EXCHANGE FROM Stock s WHERE s.LISTED_ON_EXCHANGE=m.RIC);
+-------------------------+
| NAME |
+-------------------------+
| Tokyo Stock Exchange |
| NASDAQ |
| New York Stock Exchange |
| Mumbai Stock Exchange |
+-------------------------+
4 rows IN SET (0.00 sec)
+-------------------------+
| NAME |
+-------------------------+
| Tokyo Stock Exchange |
| NASDAQ |
| New York Stock Exchange |
| Mumbai Stock Exchange |
+-------------------------+
4 rows IN SET (0.00 sec)
Here inner query volition execute for every Market every bit RIC volition endure changed for every market.
Difference betwixt Correlated in addition to NonCorrelated Subquery
Now nosotros bring seen correlated in addition to noncorrelated subqueries in addition to at that topographic point illustration its much easier to sympathise difference betwixt correlated vs noncorrelated queries. By the means this is too i of the pop sql interview interrogation in addition to its expert to know few differences:
1.In instance of correlated subquery inner query depends on outer query acre inwards instance of noncorrelated query inner query or subquery doesn't depends on outer query in addition to run past times its own.
2.In instance of correlated subquery, outer query executed earlier inner query or subquery acre inwards instance of NonCorrelated subquery inner query executes earlier outer query.
3.Correlated Sub-queries are slower than non correlated subquery in addition to should endure avoided inwards favor of sql joins.
4.Common illustration of correlated subquery is using exits in addition to non exists keyword acre non correlated query generally purpose IN or NOT IN keywords.
SubQuery vs Join inwards SQL
Any information which you lot shout out upward from database using subquery tin endure retrieved past times using dissimilar types bone joins also. Since SQL is flexible in addition to it provides dissimilar means of doing same thing. Some people honor SQL Joins confusing in addition to subquery especially noncorrelated to a greater extent than intuitive but inwards price of functioning SQL Joins are to a greater extent than efficient than subqueries.
Important points close SubQuery inwards DBMS
1.Almost whatever you lot desire to produce alongside subquery tin too endure done using join, it only affair of choice
subquery seems to a greater extent than intuitive to many user.
2.Subquery usually render an scaler value every bit effect or effect from i column if used along with
IN Clause.
3.You tin purpose subqueries inwards 4 places: subquery every bit a column inwards choose clause,
4.In instance of correlated subquery outer query gets processed earlier inner query.
Further Learning
How to handle transaction inwards Database