Explicit Sets and Renaming in SQL

1. Explicit Sets in SQL

What is an Explicit Set?

In SQL, instead of using a nested query to supply a list of values for comparison, we can directly specify an explicit set of constant values.

• The set is written inside parentheses

• Most commonly used with the IN operator

• Useful when the comparison values are known in advance

---

Syntax

WHERE attribute IN (value1, value2, value3, ...)

---

Example: 

Problem:
Retrieve the Social Security numbers of all employees who work on project numbers 1, 2, or 3.

SELECT DISTINCT Essn
FROM WORKS_ON
WHERE Pno IN (1, 2, 3);

---

Explanation

• Pno IN (1, 2, 3) checks whether Pno matches any one of the listed values

• This condition is equivalent to:

  Pno = 1 OR Pno = 2 OR Pno = 3
  

• DISTINCT ensures that duplicate employee SSNs are removed, since an employee may work on more than one of these projects

---

Why Use Explicit Sets?

✔ Simpler and more readable than nested queries
✔ Efficient for small, fixed value lists
✔ Commonly used for filtering categorical values

---

2. Renaming (Aliasing) in SQL

Why Renaming is Needed

Renaming is used to:

• Improve readability of query results

• Avoid ambiguity when the same relation appears more than once

• Provide meaningful column headings in the output

SQL uses the keyword AS for renaming.

---

3. Renaming Attributes (Column Aliasing)

Syntax

SELECT attribute AS new_name
FROM relation;

• AS is optional, but recommended for clarity

• The renamed attribute appears as a column header in the result

---

Example: 

Problem:

Retrieve the last name of each employee and the last name of his or her supervisor, with meaningful column names.

SELECT E.Lname AS Employee_name, S.Lname AS Supervisor_name
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn = S.Ssn;

---

Explanation

• The EMPLOYEE relation is used twice, so:

  • E represents the employee

  • S represents the supervisor

• E.Lname AS Employee_name

  • Renames employee’s last name in the output

• S.Lname AS Supervisor_name

  • Renames supervisor’s last name in the output

• The condition E.Super_ssn = S.Ssn establishes the supervisor relationship

---

Sample Output Format

Employee_name	Supervisor_name
Smith	Wong
Johnson	Borg

---

4. Renaming Relations (Table Aliasing)

Syntax

FROM relation AS alias

• Especially useful in:

  • Self-joins

  • Nested queries

  • Correlated queries

---

Why Aliasing is Important

✔ Avoids ambiguity
✔ Shorter, cleaner queries
✔ Essential when the same table appears multiple times

---

5. Explicit Sets vs Nested Queries

Explicit Sets	Nested Queries
Fixed values	Values from database
Simple & fast	More flexible
Used with IN	Used with IN, EXISTS, ANY
Easy to read	More powerful

---

6. Key Takeaways 

• Explicit sets are written using parentheses: (v1, v2, v3)

• IN tests membership in a set of values

• AS is used for renaming attributes and relations

• Renaming improves readability and avoids ambiguity

• Aliases are essential in self-joins