Nested Queries in SQL

 

Nested Queries in SQL

1. What is a Nested Query?

A nested query (subquery) is a complete SELECT–FROM–WHERE query that is written inside another SQL query.

  • The outer query is called the outer query

  • The inner query is called the nested query or subquery

Nested queries are useful when:

  • Values must first be retrieved from the database

  • These values are then used for comparison in another query

2. Where Can Nested Queries Appear?

A nested query can appear in:

  • WHERE clause (most common)

  • FROM clause

  • SELECT clause

  • HAVING clause

3. Nested Queries with IN Operator

IN Operator

The operator IN compares a value with a set (or multiset) of values.

  • v IN V is TRUE if v is one of the elements in V

  • V is typically the result of a nested query

Example 

Retrieve project numbers of projects where an employee with last name ‘Smith’ is either:

  • the manager, or

  • a worker

SELECT DISTINCT Pnumber
FROM PROJECT
WHERE Pnumber IN
   ( SELECT Pnumber
     FROM PROJECT, DEPARTMENT, EMPLOYEE
     WHERE Dnum = Dnumber
       AND Mgr_ssn = Ssn
       AND Lname = 'Smith' )
OR
Pnumber IN
   ( SELECT Pno
     FROM WORKS_ON, EMPLOYEE
     WHERE Essn = Ssn
       AND Lname = 'Smith' );

Explanation:

  • First nested query finds projects managed by Smith

  • Second nested query finds projects worked on by Smith

  • The outer query selects projects appearing in either result

4. Scalar Nested Queries

If a nested query returns:

  • One attribute

  • One tuple

Then the result is a single (scalar) value

In this case:

  • = can be used instead of IN

Example:

WHERE Salary = ( SELECT MAX(Salary) FROM EMPLOYEE );

5. Tuple Comparisons in Nested Queries

SQL allows tuple-valued comparisons using parentheses.

Example: Tuple Comparison with IN

SELECT DISTINCT Essn
FROM WORKS_ON
WHERE (Pno, Hours) IN
   ( SELECT Pno, Hours
     FROM WORKS_ON
     WHERE Essn = '123456789' );

Explanation:

  • The nested query returns (Pno, Hours) pairs for employee John Smith

  • The outer query finds employees who work on the same project with the same hours

  • Comparison is done tuple-by-tuple, not attribute-by-attribute

6. ANY (or SOME) and ALL Operators

ANY / SOME

  • = ANY or = SOME → equivalent to IN

  • Returns TRUE if the condition holds for at least one value

Example:

Salary > ANY (SELECT Salary FROM EMPLOYEE WHERE Dno = 5);

✔ Salary is greater than at least one employee in department 5

ALL Operator

  • Condition must hold for every value in the set

Example (Navathe):

SELECT Lname, Fname
FROM EMPLOYEE
WHERE Salary > ALL
   ( SELECT Salary
     FROM EMPLOYEE
     WHERE Dno = 5 );

Explanation:

  • Returns employees whose salary is greater than all employees in department 5

  • Equivalent to comparing with MAX(Salary)

7. Attribute Name Ambiguity and Scope Rules

When nested queries are used:

  • SQL applies scope rules, similar to programming languages

  • An unqualified attribute name refers to:

    the relation in the innermost query

To avoid ambiguity:

  • Use table aliases (tuple variables)

Example 

Retrieve employees who have a dependent with the same first name and sex

SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E
WHERE E.Ssn IN
   ( SELECT D.Essn
     FROM DEPENDENT AS D
     WHERE E.Fname = D.Dependent_name
       AND E.Sex = D.Sex );

Explanation:

  • E.Fname and E.Sex refer to the outer query

  • D.Dependent_name and D.Sex belong to DEPENDENT

  • Aliases E and D remove ambiguity

8. Correlated Nested Queries

A correlated nested query is one where:

  • The nested query references attributes of the outer query

👉 Such a nested query:

  • Is evaluated once for each tuple of the outer query

Understanding  Correlated Query

For each EMPLOYEE tuple:

  1. Execute the nested query

  2. Check dependents with:

    • Same name

    • Same sex

  3. If employee’s Ssn appears in the result → select employee

9. Equivalent Non-Nested Query

Many nested queries can be rewritten as single-block queries using joins.

Example :

SELECT E.Fname, E.Lname
FROM EMPLOYEE AS E, DEPENDENT AS D
WHERE E.Ssn = D.Essn
  AND E.Sex = D.Sex
  AND E.Fname = D.Dependent_name;

10.  Summary

  • Nested queries are queries inside other queries

  • IN compares a value with a set of values

  • Scalar subqueries return single values

  • Tuple comparisons use (A, B) IN (subquery)

  • ANY / SOME → at least one match

  • ALL → must satisfy condition for all values

  • Correlated queries reference outer query attributes

  • Aliases help avoid ambiguity and improve clarity

Comments

Post a Comment

Popular posts from this blog

Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme

 About Me Database Management Systems DBMS PCCST402 Scheme and Syllabus Module-1 Introduction to DBMS Introduction to DBMS Example  Database Database System Vs Traditional File Processing Advantages of using DBMS Database System Concepts and Architecture Data Models, Schemas and Instances   Three schema architecture and Data Independence Database Languages and Interfaces Centralized and Client Server Architectures in DBMS Classification of Database Management Systems Conceptual Data Modeling and Database Design Conceptual Modeling and the Role of the ER Model in Database Design Entities and Attributes  Company Database Application - Example Entity Types, Entity Sets, Keys, and Value Sets in the ER Model Initial Conceptual Design of the COMPANY Database Relationship Types in the ER Model Relationship Degree, Role Names, and Recursive Relationships Constraints on Binary Relationship Types Attributes of Relationship Types Weak Entity Types Summary of the Notations Used ...
Read more

Data Models, Schemas and Instances

  Data Models, Schemas and Instances  1. Data Abstraction In databases, data abstraction means hiding the complex details of how data is stored and focusing only on what is important. Different users can view data at different levels of detail depending on their needs. 2. Data Models A data model is a collection of concepts used to describe the structure of a database. It defines: Data types Relationships among data Constraints on data Operations for accessing and updating data Data models help us understand and design databases without worrying about low-level storage details. Categories of Data Models a) High-level / Conceptual Data Models Close to how users think about data. Use concepts like entities , attributes , and relationships . Example: ER (Entity–Relationship) Model . b) Representational (Implementation) Data Models In between conceptual and physical models. Easy for users to understand but still close to how data is st...
Read more

Introduction to Database Management System -DBMS

Image
In today’s digital world, almost every application—from mobile banking and e-commerce to university portals and hospital systems—depends on the efficient storage, organization, and retrieval of data. As the amount of information generated by individuals and organizations continues to grow, managing this data in a structured and meaningful way becomes essential. This is where database systems play a central role. A database can be thought of as a well-organized collection of related data, designed so that it can be easily accessed, managed, and updated. But the database itself is only one component. To manage it effectively, we rely on a specialized software package known as a Database Management System (DBMS) . A DBMS provides a systematic and automated way to store large volumes of data, enforce rules for consistency and correctness, protect data from unauthorized access, and allow multiple users to work with the data simultaneously without conflicts. A database typically has three...
Read more