The correlated subquery is ane of the tricky concepts of SQL. It's similar to recursion inwards programming which many programmers fighting to understand, but similar recursion, it besides offers the unique capability to solve many SQL query based problems e.g. second highest salary problem where yous demand to compare ane row of the tabular array to some other row. It gives yous a unlike form of power. The primary divergence betwixt a regular, non-correlated together with correlated subquery inwards SQL is inwards their working, a regular subquery only run in ane lawsuit together with provide a value or a laid upwardly of values which is used past times outer query, but correlated subquery runs for each row returned past times the outer query because the output of the whole query is based upon comparison the information returned past times ane row to the all other rows of the table. That's why it is besides really ho-hum together with to a greater extent than often than non avoided until yous don't know whatever other way to solve the problem.
One of the most pop examples of the correlated subquery is well-nigh finding the bit highest salary of an employee inwards a given table. Even though at that spot are multiple ways to solve this occupation e.g yous tin usage window functions similar row_number or rank but using regular subquery to solve this occupation is the easiest way.
Btw, fifty-fifty though yous tin solve this occupation past times using a regular query it perish tricky when Interviewer extend this occupation to notice the Nth highest salary so yous only can't perish amongst regular subquery because of unlimited nesting.
Correlated subquery solves this occupation elegantly equally shown here. It compares information returned past times outer query e.g. salary together with compares amongst other salaries to notice out exactly how many salaries are higher than this salary.
In this article, I am going to compare correlated subquery amongst the regular ane of unlike parameters e.g. their working, speed, performance, together with dependency. I'll besides give yous a skillful illustration of correlated subquery e.g. the Nth highest salary occupation together with explicate how exactly it solves the problem.
So, if interviewer inquire yous to notice the quaternary highest salary so at that spot tin alone endure at most iv salary which are equal to or greater than the 4th highest salary. This is only an example, yous tin usage correlated subquery to solve many such problems inwards the globe of information together with SQL.
In short, hither are the main divergence betwixt correlated together with non-correlated subqueries inwards SQL
1) Working
Influenza A virus subtype H5N1 non-correlated subquery is executed alone in ane lawsuit together with its final result tin endure swapped dorsum for a query, on the other hand, a correlated subquery executed multiple times, exactly in ane lawsuit for each row returned past times the outer query.
For example, next query is an illustration of non-correlated subquery:
Here the subquery is SELECT MAX(Salary) from Employee, yous tin execute together with substitute the final result of that query e.g. if subquery provide 10000 so outer query is reduced to
This is non possible amongst a correlated subquery, which needs to endure executed multiple times equally shown below:
In this example, the subquery is SELECT COUNT(Salary) FROM Employee p WHERE p.salary >= e.salary, yous cannot swap it's value for the outer query because it needs to endure executed for each employee.
Let's state the offset row of employee has salary 5000, inwards this case, e.salary volition endure 500 together with subquery volition be
together with subquery volition notice how many salaries are higher than 5000 if count provide ii so it's the second highest salary. This logic needs to endure executed for each row outer query volition process.
2) Dependency
Influenza A virus subtype H5N1 correlated subquery depends upon the outer query together with cannot execute inwards isolation, but a regular or non-correlated subquery doesn't depend on the outer query together with tin execute inwards isolation.
From the to a higher house example, yous tin come across that a correlated subquery i.e. SELECT COUNT(Salary) FROM Employee p WHERE p.salary >= e.salary depends upon outer query because it needs the value of e.salary, which comes from tabular array listed on outer query.
On the other hand, regular subquery, SELECT MAX(Salary) FROM Employee doesn't depends upon the outer query together with tin endure executed inwards isolation or independently of the outer query. You tin read a skillful SQL majority to larn this to a greater extent than exceptional e.g. Head First SQL past times Lynn Beighley.
3) Speed together with Performance
A correlated subquery is much slower than the non-correlated subquery because inwards former, the inner query executes for each row of the outer query. This agency if your tabular array has n rows so whole processing volition receive got the n * n = n^2 time, equally compared to 2n times taken past times a non-correlated subquery.
This happens because to execute non-correlated subquery yous demand to evidence only n rows of the tabular array together with similar to execute the outer query yous demand to evidence n rows, so inwards full n + n = 2n rows.
This is the argue yous should endure really careful using a correlated subquery amongst large tables e.g. tables amongst millions of rows because that tin receive got a long fourth dimension together with could potentially block other jobs together with queries from accessing the table.
In many cases, yous tin supplant correlated subquery amongst inner join which would final result inwards meliorate performance. For example, to notice all employees whose salary is greater than the average salary of the subdivision yous tin write next correlated subquery:
Now, yous tin convert this correlated subquery to a JOIN based query for meliorate functioning equally shown below:
If yous desire to larn to a greater extent than well-nigh how changing a query from subquery to bring together final result inwards meliorate functioning together with other pocket-size changes which gives large functioning boost, I strongly propose yous to read a skillful majority on SQL query together with functioning optimization e.g. SQL Performance Explained past times Markus Winand.
That's all well-nigh the difference betwixt correlated together with non-correlated subquery inwards SQL. You receive got learned that correlated subquery is executed for each row returned past times an outer query, which makes it really slow, but at the same fourth dimension gives it the might to compare ane row of the tabular array to other rows of the table. That's why sometimes alone solution possible was alone past times using a correlated subquery.
On the other paw regular or non-correlated subquery provide a final result which is so used past times the outer query. It alone executed ane fourth dimension together with non for every row returned past times the outer query, so it is faster than a correlated subquery.
Other SQL Interview Questions article yous may similar to explore
Thanks for reading this article so far. If yous similar this SQL Interview question together with explanation so delight part amongst your friends together with colleagues. If yous receive got whatever question, proffer or feedback so delight drib a comment together with I'll sweat to respond it.
P.S. - If yous are looking for online training/course to larn SQL from scratch, I propose yous joining Introduction to SQL past times Jon Flanders. It's ane of the best sourse to larn SQL fundamentals e.g. join, subquery, aggregate functions, window functions, gropuing data, advanced filtering together with SQL query optimization.
One of the most pop examples of the correlated subquery is well-nigh finding the bit highest salary of an employee inwards a given table. Even though at that spot are multiple ways to solve this occupation e.g yous tin usage window functions similar row_number or rank but using regular subquery to solve this occupation is the easiest way.
Btw, fifty-fifty though yous tin solve this occupation past times using a regular query it perish tricky when Interviewer extend this occupation to notice the Nth highest salary so yous only can't perish amongst regular subquery because of unlimited nesting.
Correlated subquery solves this occupation elegantly equally shown here. It compares information returned past times outer query e.g. salary together with compares amongst other salaries to notice out exactly how many salaries are higher than this salary.
Difference betwixt Correlated together with Regular Subquery inwards SQL
The divergence betwixt correlated together with regular subquery is besides a frequently asked SQL interview question. Mostly asked on telephonic interview where they cannot inquire yous to solve query together with banking concern fit the fundamentals together with theoretical concepts.In this article, I am going to compare correlated subquery amongst the regular ane of unlike parameters e.g. their working, speed, performance, together with dependency. I'll besides give yous a skillful illustration of correlated subquery e.g. the Nth highest salary occupation together with explicate how exactly it solves the problem.
So, if interviewer inquire yous to notice the quaternary highest salary so at that spot tin alone endure at most iv salary which are equal to or greater than the 4th highest salary. This is only an example, yous tin usage correlated subquery to solve many such problems inwards the globe of information together with SQL.
1) Working
Influenza A virus subtype H5N1 non-correlated subquery is executed alone in ane lawsuit together with its final result tin endure swapped dorsum for a query, on the other hand, a correlated subquery executed multiple times, exactly in ane lawsuit for each row returned past times the outer query.
For example, next query is an illustration of non-correlated subquery:
SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN ( SELECT MAX(Salary) FROM Employee)
Here the subquery is SELECT MAX(Salary) from Employee, yous tin execute together with substitute the final result of that query e.g. if subquery provide 10000 so outer query is reduced to
SELECT MAX(Salary) from Employee where Salary NOT IN (10000).
This is non possible amongst a correlated subquery, which needs to endure executed multiple times equally shown below:
SELECT e.Name, e.Salary FROM Employee e WHERE 2 = ( SELECT COUNT(Salary) FROM Employee p WHERE p.salary >= e.salary)
In this example, the subquery is SELECT COUNT(Salary) FROM Employee p WHERE p.salary >= e.salary, yous cannot swap it's value for the outer query because it needs to endure executed for each employee.
Let's state the offset row of employee has salary 5000, inwards this case, e.salary volition endure 500 together with subquery volition be
SELECT COUNT(Salary) FROM Employee p WHERE p.salary >= 5000
together with subquery volition notice how many salaries are higher than 5000 if count provide ii so it's the second highest salary. This logic needs to endure executed for each row outer query volition process.
2) Dependency
Influenza A virus subtype H5N1 correlated subquery depends upon the outer query together with cannot execute inwards isolation, but a regular or non-correlated subquery doesn't depend on the outer query together with tin execute inwards isolation.
From the to a higher house example, yous tin come across that a correlated subquery i.e. SELECT COUNT(Salary) FROM Employee p WHERE p.salary >= e.salary depends upon outer query because it needs the value of e.salary, which comes from tabular array listed on outer query.
On the other hand, regular subquery, SELECT MAX(Salary) FROM Employee doesn't depends upon the outer query together with tin endure executed inwards isolation or independently of the outer query. You tin read a skillful SQL majority to larn this to a greater extent than exceptional e.g. Head First SQL past times Lynn Beighley.
3) Speed together with Performance
A correlated subquery is much slower than the non-correlated subquery because inwards former, the inner query executes for each row of the outer query. This agency if your tabular array has n rows so whole processing volition receive got the n * n = n^2 time, equally compared to 2n times taken past times a non-correlated subquery.
This happens because to execute non-correlated subquery yous demand to evidence only n rows of the tabular array together with similar to execute the outer query yous demand to evidence n rows, so inwards full n + n = 2n rows.
This is the argue yous should endure really careful using a correlated subquery amongst large tables e.g. tables amongst millions of rows because that tin receive got a long fourth dimension together with could potentially block other jobs together with queries from accessing the table.
In many cases, yous tin supplant correlated subquery amongst inner join which would final result inwards meliorate performance. For example, to notice all employees whose salary is greater than the average salary of the subdivision yous tin write next correlated subquery:
SELECT e.id, e.name FROM Employee e WHERE salary > ( SELECT AVG(salary) FROM Employee p WHERE p.department = e.department)
Now, yous tin convert this correlated subquery to a JOIN based query for meliorate functioning equally shown below:
SELECT e.id, e.name FROM Employee INNER JOIN (SELECT department, AVG(salary) AS department_average FROM Employee GROUP BY department) AS t ON e.department = t.department WHERE e.salary > t.department_average;
If yous desire to larn to a greater extent than well-nigh how changing a query from subquery to bring together final result inwards meliorate functioning together with other pocket-size changes which gives large functioning boost, I strongly propose yous to read a skillful majority on SQL query together with functioning optimization e.g. SQL Performance Explained past times Markus Winand.
That's all well-nigh the difference betwixt correlated together with non-correlated subquery inwards SQL. You receive got learned that correlated subquery is executed for each row returned past times an outer query, which makes it really slow, but at the same fourth dimension gives it the might to compare ane row of the tabular array to other rows of the table. That's why sometimes alone solution possible was alone past times using a correlated subquery.
On the other paw regular or non-correlated subquery provide a final result which is so used past times the outer query. It alone executed ane fourth dimension together with non for every row returned past times the outer query, so it is faster than a correlated subquery.
Other SQL Interview Questions article yous may similar to explore
- How to notice the bit highest salary inwards MySQL? (solution)
- What is the divergence betwixt UNION together with UNION ALL inwards SQL? (answer)
- The divergence betwixt TRUNCATE together with DELETE inwards SQL? (answer)
- The divergence betwixt self together with equi-join inwards SQL? (answer)
- What is the divergence betwixt View together with Materialized View inwards Oracle database? (answer)
- How to notice bit highest salary inwards Oracle using ROW_NUMBER? (answer)
- The divergence betwixt WHERE together with HAVING clause inwards SQL? (answer)
- The divergence betwixt LEFT together with RIGHT OUTER JOIN inwards SQL? (answer)
- How to notice duplicate records inwards a table? (query)
Thanks for reading this article so far. If yous similar this SQL Interview question together with explanation so delight part amongst your friends together with colleagues. If yous receive got whatever question, proffer or feedback so delight drib a comment together with I'll sweat to respond it.
P.S. - If yous are looking for online training/course to larn SQL from scratch, I propose yous joining Introduction to SQL past times Jon Flanders. It's ane of the best sourse to larn SQL fundamentals e.g. join, subquery, aggregate functions, window functions, gropuing data, advanced filtering together with SQL query optimization.
0 Response to "Difference betwixt Correlated together with Non-Correlated Subquery inwards SQL"
Posting Komentar