How to identify correlated subquery and what is the replacement of it

Bikram

Hi Team,

I'm trying to explore a correlated subquery, however, it's a little bit confusing for me to recognize a correlated subquery.

I also like to know the alternative to the Correlated subquery.

It would be better if I got an answer with some example

Thanks,

Bikram

Frank Kulash

Hi, @Bikram

I'm trying to explore a correlated subquery,

Post the complete query you're trying to explore, along with a little sample data (CREATE TABLE and INSERT statements) for all the tables involved.

A correlated sub-query is any query that references a table that is not in its FROM clause. For example: the following query is one way to get information about departments that have at least one employee with a salary of 3000 or more.

SELECT    d.*
FROM	  scott.dept  d
WHERE	  EXISTS (	-- Begin sub-query
	  	     SELECT  0
		     FROM    scott.emp  e
		     WHERE   e.deptno  =  d.deptno
		     AND     e.sal     >= 3000
	  	 )	-- End sub-query
ORDER BY  d.deptno
;

In the FROM clause of the sub-query, the only table is scott.emp (with e as its alias). Notice that the sub-query references a column from another table (with d as its alias). Therefore, the sub-query is correlated. If a sub-query uses a table alias that is not defined in the same sub-query, then it is correlated.

However, if a sub-query does NOT use a table alias that is not defined in the same sub-query, then it is NOT necessarily uncorrelated, because columns are not always qualified with an alias. If you don't know all the columns in all the tables you're using, then you can't always be sure if a sub-query is correlated or not. This is one of the many reasons why you should use table aliases on all columns in a statement that uses multiple tables.

I also like to know the alternative to the Correlated subquery.

An uncorrelated sub-query is one alternative. JOIN is another. Sometimes, a corelated sub-query can be replaced with analytic functions.

Frank Kulash

I also like to know the alternative to the Correlated subquery.

Here are four different alternatives to using a correlated sub-query. All of the queries below display the deptno, ename and sal of the employee(s) in each department with the highest sal.

PROMPT	==========  Correlated Sub-Query  ==========

SELECT    deptno, ename, sal
FROM	  scott.emp  m				-- M is for Main query
WHERE	  NOT EXISTS (
	      	         SELECT  0
			 FROM	 scott.emp  s	-- S is for Sub-query
			 WHERE	 s.deptno  = m.deptno
			 AND	 s.sal	   > m.sal
	      	     )
ORDER BY  deptno, ename
;


PROMPT	==========  Un-Correlated Sub-Query  ==========

SELECT    deptno, ename, sal
FROM	  scott.emp
WHERE	  (deptno, sal)  IN  (
	      	                 SELECT    deptno, MAX (sal)
			     	 FROM	   scott.emp
			     	 GROUP BY  deptno
	      	     	     )
ORDER BY  deptno, ename
;


PROMPT	==========  JOIN  ==========

SELECT    l.deptno, l.ename, l.sal
FROM	  scott.emp  l  -- L is for Lower sal
LEFT JOIN scott.emp  h  -- H is for Higher sal
     	  	        ON   h.deptno  = l.deptno
			AND  h.sal     > l.sal
WHERE     h.deptno  IS NULL
ORDER BY  l.deptno, l.ename
;


PROMPT  ==========  Analytic Function ==========

WITH    got_max_sal    AS
(
	SELECT  deptno, ename, sal
	,	MAX (sal) OVER (PARTITION BY deptno)	AS max_sal
	FROM	scott.emp
)
SELECT    deptno, ename, sal
FROM	  got_max_sal
WHERE	  sal  = max_sal
ORDER BY  deptno, ename
;


PROMPT	=========  MATCH_RECOGNIZE  ==========

SELECT    deptno, ename, sal
FROM	  scott.emp
MATCH_RECOGNIZE
          (
	      PARTITION BY  deptno
	      ORDER BY	    sal  DESC
	      ALL ROWS PER MATCH
	      PATTERN       ( ^ top_sal + )
	      DEFINE   	    top_sal 	 AS  sal  = FIRST (sal)
	  )
ORDER BY  deptno, ename
;

I'm assuming that deptno and sal cannot be NULL. All of the queries above get the same output, that is:

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 FORD             3000
        20 SCOTT            3000
        30 BLAKE            2850

Frank Kulash

Hi, @Bikram

I'm trying to explore a correlated subquery,

Post the complete query you're trying to explore, along with a little sample data (CREATE TABLE and INSERT statements) for all the tables involved.

A correlated sub-query is any query that references a table that is not in its FROM clause. For example: the following query is one way to get information about departments that have at least one employee with a salary of 3000 or more.

SELECT    d.*
FROM	  scott.dept  d
WHERE	  EXISTS (	-- Begin sub-query
	  	     SELECT  0
		     FROM    scott.emp  e
		     WHERE   e.deptno  =  d.deptno
		     AND     e.sal     >= 3000
	  	 )	-- End sub-query
ORDER BY  d.deptno
;

In the FROM clause of the sub-query, the only table is scott.emp (with e as its alias). Notice that the sub-query references a column from another table (with d as its alias). Therefore, the sub-query is correlated. If a sub-query uses a table alias that is not defined in the same sub-query, then it is correlated.

However, if a sub-query does NOT use a table alias that is not defined in the same sub-query, then it is NOT necessarily uncorrelated, because columns are not always qualified with an alias. If you don't know all the columns in all the tables you're using, then you can't always be sure if a sub-query is correlated or not. This is one of the many reasons why you should use table aliases on all columns in a statement that uses multiple tables.

I also like to know the alternative to the Correlated subquery.

An uncorrelated sub-query is one alternative. JOIN is another. Sometimes, a corelated sub-query can be replaced with analytic functions.

Frank Kulash

I also like to know the alternative to the Correlated subquery.

Here are four different alternatives to using a correlated sub-query. All of the queries below display the deptno, ename and sal of the employee(s) in each department with the highest sal.

PROMPT	==========  Correlated Sub-Query  ==========

SELECT    deptno, ename, sal
FROM	  scott.emp  m				-- M is for Main query
WHERE	  NOT EXISTS (
	      	         SELECT  0
			 FROM	 scott.emp  s	-- S is for Sub-query
			 WHERE	 s.deptno  = m.deptno
			 AND	 s.sal	   > m.sal
	      	     )
ORDER BY  deptno, ename
;


PROMPT	==========  Un-Correlated Sub-Query  ==========

SELECT    deptno, ename, sal
FROM	  scott.emp
WHERE	  (deptno, sal)  IN  (
	      	                 SELECT    deptno, MAX (sal)
			     	 FROM	   scott.emp
			     	 GROUP BY  deptno
	      	     	     )
ORDER BY  deptno, ename
;


PROMPT	==========  JOIN  ==========

SELECT    l.deptno, l.ename, l.sal
FROM	  scott.emp  l  -- L is for Lower sal
LEFT JOIN scott.emp  h  -- H is for Higher sal
     	  	        ON   h.deptno  = l.deptno
			AND  h.sal     > l.sal
WHERE     h.deptno  IS NULL
ORDER BY  l.deptno, l.ename
;


PROMPT  ==========  Analytic Function ==========

WITH    got_max_sal    AS
(
	SELECT  deptno, ename, sal
	,	MAX (sal) OVER (PARTITION BY deptno)	AS max_sal
	FROM	scott.emp
)
SELECT    deptno, ename, sal
FROM	  got_max_sal
WHERE	  sal  = max_sal
ORDER BY  deptno, ename
;


PROMPT	=========  MATCH_RECOGNIZE  ==========

SELECT    deptno, ename, sal
FROM	  scott.emp
MATCH_RECOGNIZE
          (
	      PARTITION BY  deptno
	      ORDER BY	    sal  DESC
	      ALL ROWS PER MATCH
	      PATTERN       ( ^ top_sal + )
	      DEFINE   	    top_sal 	 AS  sal  = FIRST (sal)
	  )
ORDER BY  deptno, ename
;

I'm assuming that deptno and sal cannot be NULL. All of the queries above get the same output, that is:

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 FORD             3000
        20 SCOTT            3000
        30 BLAKE            2850

Bikram

@Frank Kulash I really appreciate your effort for explaining the concepts and putting all the examples.