Joined Tables in SQL and Outer Joins

1. Motivation for Joined Tables

In SQL, information is often distributed across multiple relations. To retrieve meaningful data, we combine tables using JOIN operations.

Originally, joins were written by:

• Listing tables in the FROM clause

• Specifying join conditions in the WHERE clause

However, this approach mixes join conditions and selection conditions, making queries harder to read.

👉 Joined tables were introduced to:

• Explicitly represent joins in the FROM clause

• Improve clarity and readability

• Match relational algebra join operations more closely

---

2. Joined Tables Using INNER JOIN

Example: 

Problem:
Retrieve the name and address of employees who work for the Research department.

SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber)
WHERE Dname = 'Research';

Explanation

• EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber

  • Performs an INNER JOIN

  • Matches employee tuples with department tuples

• The result is a temporary joined table

• WHERE Dname = 'Research' filters rows after the join

📌 The attributes of the joined table include:

• All attributes of EMPLOYEE

• Followed by all attributes of DEPARTMENT

---

3. NATURAL JOIN

What is NATURAL JOIN?

A NATURAL JOIN:

• Automatically joins tables using attributes with the same name

• No explicit join condition is written

• Common attributes appear only once in the result

---

Example: Query Q1B

SELECT Fname, Lname, Address
FROM (EMPLOYEE NATURAL JOIN
      (DEPARTMENT AS DEPT (Dname, Dno, Mssn, Msdate)))
WHERE Dname = 'Research';

Explanation

• The DEPARTMENT relation is:

  • Renamed as DEPT

  • Attributes are renamed so Dno matches EMPLOYEE.Dno

• Since both tables now have an attribute named Dno,
  SQL implicitly performs:

  EMPLOYEE.Dno = DEPT.Dno
  

⚠️ Caution: NATURAL JOIN can be dangerous if unintended attribute names match.

---

4. INNER JOIN vs OUTER JOIN

INNER JOIN (Default)

• Only tuples with matching values appear in the result

• Tuples with NULL in join attributes are excluded

Example:

• Employees without supervisors (Super_ssn IS NULL) are not shown

---

5. OUTER JOIN

Outer joins were introduced to avoid loss of information.

They allow tuples with no matching partner to still appear in the result.

---

Types of OUTER JOIN

Join Type	Description
LEFT OUTER JOIN	Keeps all tuples from the left table
RIGHT OUTER JOIN	Keeps all tuples from the right table
FULL OUTER JOIN	Keeps all tuples from both tables

Missing attribute values are padded with NULLs.

---

Example: (LEFT OUTER JOIN)

SELECT E.Lname AS Employee_name,
       S.Lname AS Supervisor_name
FROM (EMPLOYEE AS E
LEFT OUTER JOIN EMPLOYEE AS S
ON E.Super_ssn = S.Ssn);

Explanation

• Every employee is included

• If an employee has no supervisor:

  • Supervisor_name is NULL

• Useful for complete reporting

📌 This is a self-join with outer join semantics

---

6. NATURAL OUTER JOIN

If join attributes have the same name, SQL allows:

NATURAL LEFT OUTER JOIN
NATURAL RIGHT OUTER JOIN
NATURAL FULL OUTER JOIN

These combine the behaviors of:

• NATURAL JOIN

• OUTER JOIN

---

7. CROSS JOIN (Cartesian Product)

CROSS JOIN

• Produces all possible combinations of tuples

• Equivalent to relational algebra Cartesian Product

⚠️ Should be used with extreme care due to:

• Very large result size

• Performance issues

---

8. Multiway Joins (Nested Joined Tables)

Joined tables can themselves be joined with other tables.

Example: Query 

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM ((PROJECT JOIN DEPARTMENT ON Dnum = Dnumber)
      JOIN EMPLOYEE ON Mgr_ssn = Ssn)
WHERE Plocation = 'Stafford';

Explanation

1. PROJECT is joined with DEPARTMENT

2. The result is then joined with EMPLOYEE

3. This forms a three-table (multiway) join

✔ Clean
✔ Modular
✔ Easy to extend

---

9. Non-Standard (Legacy) Outer Join Syntax

Some systems (e.g., Oracle) used older syntax:

Operator	Meaning
+=	LEFT OUTER JOIN
=+	RIGHT OUTER JOIN
+=+	FULL OUTER JOIN

---

Example: Query (Oracle-style)

SELECT E.Lname, S.Lname
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.Super_ssn += S.Ssn;

⚠️ This syntax:

• Is non-standard

• Not portable

• Deprecated in modern SQL

---

10. Summary 

• Joined tables make joins explicit and readable

• INNER JOIN is the default

• OUTER JOIN prevents tuple loss

• LEFT OUTER JOIN is most commonly used

• NATURAL JOIN uses same-named attributes

• Multiway joins combine more than two tables

• CROSS JOIN creates Cartesian product

• Old outer join syntax should be avoided