Unit 1: Database Concepts (RDBMS)

Chapter 7: Multi-Table Queries and SQL Joins

1. Why Multi-Table Queries?

Relational databases normalize data across multiple related tables to eliminate data redundancy. A Join combines tuples from two or more tables based on a common related column (typically Primary Key in one table and Foreign Key in the other).

2. Cartesian Product (Cross Join)

A Cartesian Product pairs every row of Table $A$ with every row of Table $B$ without any matching condition. - If Table $A$ has Degree $D_1$ and Cardinality $C_1$, and Table $B$ has Degree $D_2$ and Cardinality $C_2$: - $\text{Degree}(A \times B) = D_1 + D_2$ - $\text{Cardinality}(A \times B) = C_1 \times C_2$

-- Cartesian Product Syntax
SELECT * FROM Student, Department;

3. Equi-Join

An Equi-Join is a join condition using an equality operator (=) on common key attributes to filter out meaningless combinations from a Cartesian product.

Syntax

SELECT Table1.col1, Table2.col2
FROM Table1, Table2
WHERE Table1.common_key = Table2.common_key;

Table Aliases for Clean Syntax

SELECT S.RollNo, S.Name, D.Dept_Name, D.HOD
FROM Student S, Department D
WHERE S.Dept_ID = D.Dept_ID;

4. ANSI SQL JOIN Syntax (INNER JOIN)

The modern ANSI standard uses the explicit JOIN ... ON syntax:

SELECT S.RollNo, S.Name, S.Marks, D.Dept_Name
FROM Student S
INNER JOIN Department D
ON S.Dept_ID = D.Dept_ID
WHERE S.Marks >= 80;

5. Natural Join

A Natural Join compares all columns with matching names in both tables and automatically eliminates redundant duplicate join columns from the result.

SELECT * FROM Student NATURAL JOIN Department;

6. Comprehensive Board Question Walkthrough

Consider the two tables below:

Table: DOCTOR | Doc_ID | Doc_Name | Department | OPD_Fee | | :--- | :--- | :--- | :--- | | D101 | Dr. Verma | Cardiology | 800 | | D102 | Dr. Mehta | Neurology | 1000 | | D103 | Dr. Ray | Pediatrics | 600 |

Table: PATIENT | Pat_No | Pat_Name | Age | Doc_ID | | :--- | :--- | :--- | :--- | | P01 | Aman | 45 | D101 | | P02 | Rekha | 32 | D103 | | P03 | Vijay | 58 | D101 | | P04 | Meera | 24 | D102 |

Queries:

1. Display Patient Name, Age, and their Doctor's Name: sql SELECT P.Pat_Name, P.Age, D.Doc_Name FROM PATIENT P, DOCTOR D WHERE P.Doc_ID = D.Doc_ID;
2. Display Doctor Name and Patient Name where OPD Fee is greater than 700: sql SELECT D.Doc_Name, P.Pat_Name, D.OPD_Fee FROM DOCTOR D, PATIENT P WHERE D.Doc_ID = P.Doc_ID AND D.OPD_Fee > 700;
3. What is the degree and cardinality of the Cartesian product of DOCTOR and PATIENT?
4. Degree of DOCTOR = 4, Cardinality = 3
5. Degree of PATIENT = 4, Cardinality = 4
6. $\text{Result Degree} = 4 + 4 = 8$
7. $\text{Result Cardinality} = 3 \times 4 = 12$