Home » DBMS

Nested Queries, Correlated Nested Queries and Set Comparison Operators in DBMS

In this tutorial, we will learn about the nested queries, correlated nested queries and set comparison operators with examples in DBMS. By Anushree Goswami Last updated : May 27, 2023

Nested Queries

A query embedded in a query. This type of relation is termed as Nested Query and the Embedded Query is termed as a subquery.

Example

To find the names of employee who are department Id 103.

SELECT E.ename
FROM Employee E
WHERE E.id IN (SELECTD.id FROM Department D WHERE D.id = 103)

Correlated Nested Queries

These types of queries are nested queries which are independent or depend only on the same row of an outer query being an embedded query.

Example

To find the names of employee who are department Id 103.

SELECT E.ename
FROM Employee E
WHERE EXISTS (SELECT *x FROM Department D WHERE D.id = 103 AND D.id = E.id)

Set Comparison Operators

There are different types of set comparison operators like EXISTS, IN and UNIQUE. SQL also supports op ANY and op ALL, where op means arithmetic comparison operators such as <, <=, =, <>, >=, >. SOME are also one of the set comparison operators but it is similar to ANY.

Example

Find Employees whose salary is greater than an employee named Shivam.

SELECT E.id
FROM Employee E
WHERE E.salary>ANY (SELECTE2.salary FROM Employee E2 WHERE E2.ename = 'Shivam')