SQL | Correlated Subquery

In this article, you'll understand the basic concept of Correlated Subquery. They are used to select data from a table referenced in the outer query.


First, let us understand what is a Subquery?

  • SQL provides a subquery technique, which involves placing an inner query (Eg. SELECT * FROM (Table name) WHERE) within a WHERE or HAVING(The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions) clause of another (outer) query.

  • The inner query renders a set of one or more than one value for the search condition of the outer query. Such queries are referred to as Correlated Subquery.

  • Subqueries can be nested multiple times and that is the foremost example of SQL being a set-oriented language.

Now, you might want to know the nuance of Correlated Subquery?

  • The subquery is known as correlated because the subquery is related to the outer query.

  • In this type of query, a table alias (also called a correlation name) must be used to specify which table reference is to be used.


Syntax :

SELECT column1, column2, ..., column n
FROM table1 outer
WHERE column1 operator
(SELECT column1, column2
FROM table2
WHERE expr1 = outer.expr2);

Example :

The following correlated subqueries retrieve prpy_no, prpy_cost, cust_id, and broker_id from the table Properties ( 'P' and 'B' are the aliases of Properties and Brokers table) with below-given conditions -

the broker_id of Properties table must be the same broker_id of Brokers table and broker_name of Brokers table must be Jignesh.


Properties Brokers










SELECT P.prpy_no, P.prpy_cost, P.cust_id, P.broker_id FROM Properties P WHERE P.broker_id = (SELECT B.broker_id FROM Brokers B WHERE B.broker_name='Jignesh');

Output :

prpy_no    prpy_cost    cust_id    broker_id
---------------------------------------------
1          150000       C1         B3          
3          700000       C3         B3      

In the next article, we'll learn about the different operators which are used with Correlated

Subquery.

Happy Learning!

Follow us on Instagram @programmersdoor

Join us on Telegram @programmersdoor


Do comment if you find any error in the above article, or if you have an easier way to explain the topic.

Follow Programmers Door for more.

#blog #interview #placement #learn #computer #science #CorrelatedSubquery #Subquery #DBMS

40 views

        Contact Us

programmersdoor@gmail.com

  • LinkedIn
  • Facebook
  • Instagram

©2023 by Programmers Door