Difference Betwixt Correlated In Addition To Non-Correlated Subquery Inwards Sql

The correlated subquery is i of the tricky concepts of SQL. It's similar to recursion inwards programming which many programmers fighting to understand, but similar recursion, it too offers the unique capability to solve many SQL query based problems e.g. second highest salary problem where you lot demand to compare i row of the tabular array to some other row. It gives you lot a dissimilar form of power. The primary divergence betwixt a regular, non-correlated as well as correlated subquery inwards SQL is inwards their working, a regular subquery simply run i time as well as provide a value or a laid upward 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 i row to the all other rows of the table. That's why it is too real tiresome as well as to a greater extent than often than non avoided until you lot don't know whatever other way to solve the problem.

One of the most pop examples of the correlated subquery is almost finding the instant highest salary of an employee inwards a given table. Even though at that topographic point are multiple ways to solve this occupation e.g you lot tin forcefulness out exercise window functions similar row_number or rank but using regular subquery to solve this occupation is the easiest way.

Btw, fifty-fifty though   you lot tin forcefulness out solve this occupation past times using a regular query it give-up the ghost tricky when Interviewer extend this occupation to honour the Nth highest salary as well as then you lot simply can't give-up the ghost 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 as well as compares amongst other salaries to honour out exactly how many salaries are higher than this salary.

Difference betwixt Correlated as well as Regular Subquery inwards SQL

The divergence betwixt correlated as well as regular subquery is too a frequently asked SQL interview question. Mostly asked on telephonic interview where they cannot enquire you lot to solve query as well as banking enterprise fit the fundamentals as well as theoretical concepts.

In this article, I am going to compare correlated subquery amongst the regular i of dissimilar parameters e.g. their working, speed, performance, as well as dependency. I'll too give you lot a skilful illustration of correlated subquery e.g. the Nth highest salary occupation as well as explicate how exactly it solves the problem.

So, if interviewer enquire you lot to honour the fourth highest salary as well as then at that topographic point tin forcefulness out alone live on at most iv salary which are equal to or greater than the 4th highest salary.  This is simply an example, you lot tin forcefulness out exercise correlated subquery to solve many such problems inwards the footing of information as well as SQL.

In short, hither are the main divergence betwixt correlated as well as non-correlated subqueries inwards SQL


1) Working 
H5N1 non-correlated subquery is executed alone i time as well as its upshot tin forcefulness out live on swapped dorsum for a query, on the other hand, a correlated subquery executed multiple times, just i time 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, you lot tin forcefulness out execute as well as substitute the upshot of that query e.g. if subquery provide 10000 as well as then 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 live on 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, you lot cannot swap it's value for the outer query because it needs to live on executed for each employee.

Let's tell the get-go row of employee has salary 5000, inwards this case, e.salary volition live on 500 as well as subquery volition be

SELECT COUNT(Salary) FROM Employee p WHERE p.salary >= 5000

as well as subquery volition honour how many salaries are higher than 5000 if count provide ii as well as then it's the second highest salary. This logic needs to live on executed for each row outer query volition process.



2) Dependency
H5N1 correlated subquery depends upon the outer query as well as cannot execute inwards isolation, but a regular or non-correlated subquery doesn't depend on the outer query as well as tin forcefulness out execute inwards isolation.

From the inwards a higher house example, you lot tin forcefulness out run into 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 as well as tin forcefulness out live on executed inwards isolation or independently of the outer query. You tin forcefulness out read a skilful SQL mass to larn this to a greater extent than item e.g. Head First SQL past times Lynn Beighley.

3) Speed as well as 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 as well as then whole processing volition accept 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 you lot demand to examine simply n rows of the tabular array as well as similar to execute the outer query you lot demand to examine n rows, so inwards full n + n = 2n rows.

This is the argue you lot should live on real careful using a correlated subquery amongst large tables e.g. tables amongst millions of rows because that tin forcefulness out accept a long fourth dimension as well as could potentially block other jobs as well as queries from accessing the table.

In many cases, you lot tin forcefulness out supplant correlated subquery amongst inner join which would upshot inwards improve performance. For example, to honour all employees whose salary is greater than the average salary of the subdivision you lot tin forcefulness out 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, you lot tin forcefulness out convert this correlated subquery to a JOIN based query for improve surgery 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 you lot desire to larn to a greater extent than almost how changing a query from subquery to bring together upshot inwards improve surgery as well as other modest changes which gives large surgery boost, I strongly propose you lot to read a skilful mass on SQL query as well as surgery optimization e.g. SQL Performance Explained past times Markus Winand.

That's all almost the difference betwixt correlated as well as 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 real slow, but at the same fourth dimension gives it the might to compare i 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 mitt regular or non-correlated subquery provide a upshot which is as well as then used past times the outer query. It alone executed i fourth dimension as well as non for every row returned past times the outer query, thence it is faster than a correlated subquery.


Other SQL Interview Questions article you lot may similar to explore

• How to honour the instant highest salary inwards MySQL? (solution)
• What is the divergence betwixt UNION as well as UNION ALL inwards SQL? (answer)
• The divergence betwixt TRUNCATE as well as DELETE inwards SQL? (answer)
• The divergence betwixt self as well as equi-join inwards SQL? (answer)
• What is the divergence betwixt View as well as Materialized View inwards Oracle database? (answer)
• How to honour instant highest salary inwards Oracle using ROW_NUMBER? (answer)
• The divergence betwixt WHERE as well as HAVING clause inwards SQL? (answer)
• The divergence betwixt LEFT as well as RIGHT OUTER JOIN inwards SQL? (answer)
• How to honour duplicate records inwards a table? (query)

Thanks for reading this article so far. If you lot similar this SQL Interview question as well as explanation as well as then delight portion amongst your friends as well as colleagues. If you lot receive got whatever question, proposition or feedback as well as then delight drib a comment as well as I'll endeavor to reply it.


Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners