.png)
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.