EXISTS and UNIQUE Functions in SQL

1. Overview

EXISTS and UNIQUE are Boolean functions in SQL:

• They return either TRUE or FALSE

• Hence, they are mainly used in the WHERE clause

• They are especially useful in nested and correlated queries

---

2. The EXISTS Function

Definition

EXISTS (subquery)

• Returns TRUE if the subquery result contains at least one tuple

• Returns FALSE if the subquery result is empty

• The actual values returned by the subquery do not matter—only whether a tuple exists

---

Key Characteristics

• Typically used with correlated nested queries

• Evaluated once for each tuple of the outer query

• Efficient because SQL stops processing the subquery as soon as one matching tuple is found

---

3. Example: Using EXISTS 

Problem

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

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

Explanation

• For each EMPLOYEE tuple:

  • The nested query searches for at least one DEPENDENT

    • Same employee (E.Ssn = D.Essn)

    • Same sex

    • Same first name

• If at least one dependent exists, EXISTS returns TRUE

• That EMPLOYEE is selected

✔ Only existence is tested, not the number of dependents

---

4. NOT EXISTS Function

Definition

NOT EXISTS (subquery)

• Returns TRUE if the subquery result is empty

• Returns FALSE if any tuple exists

---

Example: Employees with No Dependents 

SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS (
   SELECT *
   FROM DEPENDENT
   WHERE Ssn = Essn
);

Explanation

• For each EMPLOYEE:

  • The nested query retrieves all dependents related to that employee

• If no dependent is found, the subquery result is empty

• NOT EXISTS becomes TRUE → employee is selected

🧠 Interpretation:

“Select employees for whom no related dependent tuple exists.”

---

5. Using Multiple EXISTS Conditions

Example: Managers Who Have at Least One Dependent 

SELECT Fname, Lname
FROM EMPLOYEE
WHERE EXISTS (
   SELECT *
   FROM DEPENDENT
   WHERE Ssn = Essn
)
AND EXISTS (
   SELECT *
   FROM DEPARTMENT
   WHERE Ssn = Mgr_ssn
);

Explanation

An employee is selected only if:

1. They have at least one dependent

2. They manage at least one department

✔ Both conditions must be TRUE

---

Equivalent Without Nested Queries (JOIN-based)

SELECT DISTINCT E.Fname, E.Lname
FROM EMPLOYEE E
JOIN DEPENDENT D ON E.Ssn = D.Essn
JOIN DEPARTMENT DP ON E.Ssn = DP.Mgr_ssn;

---

6. EXISTS for Universal Quantification (ALL condition)

Some queries require “for all” semantics. SQL does not directly support FOR ALL, so we simulate it using:

• NOT EXISTS

• Set difference (EXCEPT)

---

7. Example: Employees Working on All Projects of Department 5 

Logical Meaning

Select employees such that there does not exist a project controlled by department 5 that the employee does not work on.

---

Method 1: Using EXCEPT 

SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS (
   ( SELECT Pnumber
     FROM PROJECT
     WHERE Dnum = 5 )
   EXCEPT
   ( SELECT Pno
     FROM WORKS_ON
     WHERE Ssn = Essn )
);

Explanation

• First subquery → all projects controlled by department 5

• Second subquery → projects worked on by the employee

• EXCEPT finds projects missing from the employee’s assignments

• If the result is empty → employee works on all projects

✔ Universal condition achieved using NOT EXISTS

---

8. Method 2: Nested NOT EXISTS 

SELECT Lname, Fname
FROM EMPLOYEE
WHERE NOT EXISTS (
   SELECT *
   FROM WORKS_ON B
   WHERE B.Pno IN (
      SELECT Pnumber
      FROM PROJECT
      WHERE Dnum = 5
   )
   AND NOT EXISTS (
      SELECT *
      FROM WORKS_ON C
      WHERE C.Essn = Ssn
        AND C.Pno = B.Pno
   )
);

Explanation

• Outer nested query checks for:

  • Any project of department 5

  • That the employee does not work on

• If no such project exists, the employee works on all projects

🧠 This mirrors tuple relational calculus logic

---

9. The UNIQUE Function

Definition

UNIQUE (subquery)

• Returns TRUE if the subquery result contains no duplicate tuples

• Returns FALSE if duplicates exist

Purpose

• Used to check whether a query result is:

  • a set (no duplicates)

  • or a multiset (bag)

⚠️ Note:

• UNIQUE is less commonly used in practice

• Not supported by all SQL implementations

---

10. Summary 

• EXISTS(Q) → TRUE if Q returns at least one tuple

• NOT EXISTS(Q) → TRUE if Q returns no tuples

• Values inside SELECT * do not matter

• EXISTS is commonly used with correlated queries

• Universal queries (“works on all”) are expressed using:

  • NOT EXISTS

  • EXCEPT

• UNIQUE(Q) checks for absence of duplicate tuples