SQL: 2 books in 1: Advanced and Elite Level SQL From The Ground Up by Dvorjak Keith

Author:Dvorjak, Keith [Dvorjak, Keith]
Language: eng
Format: azw3, epub, pdf
Published: 2019-01-27T16:00:00+00:00

Correlated vs Non correlated Subqueries

Subqueries can be used in insert and select statements. Subqueries should return a scalar value if it makes use of WHERE clause or a value from the column if it is using IN or NOT IN clause. With this we now come to difference between correlated and no correlated sub queries. A subquery which depends upon the outer query and cannot execute on its own where as in case of non-correlated Subqueries both inner and outer queries are independent of each other.

SELECT * FROM DEPT_DATA d WHERE ENGG_ID IN (SELECT ENGG_ID FROM ENGINEERI

NG_STUDENTS e WHERE e.ENGG_ID=d.ENGG_ID );

+ - - - - - - -+ - - - - - - - - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - +

| Dept_ID | HOD | NO_OF_Prof | ENGG_ID |

+ - - - - - - -+ - - - - - - - - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - +

| 100 | Miley Andrews | 7 | 1 |

| 101 | Alex Dawson | 6 | 2 |

| 103 | Anne Joseph | 5 | 4 |

| 104 | Sophia Williams | 8 | 5 |

| 105 | Olive Brown | 4 | 6 |

| 106 | Joshua Taylor | 6 | 7 |

| 107 | Ethan Thomas | 5 | 8 |

| 108 | Michael Anderson | 8 | 9 |

| 109 | Martin Jones | 5 | 10 |

+ - - - - - - -+ - - - - - - - - - - - - - - -+ - - - - - - - - - - -+ - - - - - - - +

rows in set (0.16 sec)

When the inner sub query references the outer main query, we call it correlated Subqueries. The inner subquery references the column name of the table that is in outer query.

In case of Non correlated subquery the inner subquery is independent of outer main query.

SELECT * FROM DEPT_DATA d WHERE ENGG_ID IN (SELECT ENGG_ID FROM ENGINEERI

NG_STUDENTS );

+ - - - - - - -+ - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - +

| Dept_ID | HOD | NO_OF_Prof | ENGG_ID |

+ - - - - - - -+ - - - - - - - - - - - - - - + - - - - - - - - - - + - - - - - - - - +

| 100 | Miley Andrews | 7 | 1 |

| 101 | Alex Dawson | 6 | 2 |

| 103 | Anne Joseph | 5 | 4 |

| 104 | Sophia Williams | 8 | 5 |

|

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.