## 🎯 Introduction
* In databases, data is usually stored in **multiple tables**.
* To fetch meaningful information, we **combine tables** using **JOINS**.
* To combine results of multiple queries, we use **SET OPERATIONS**.
🧠 **JOIN = combine tables**
🧠 **SET = combine query results**
---
## 🧩 CONSTANT TABLES (Used for all examples)
### EMP
| empno | ename | deptno | salary |
| ----- | -------- | ------ | ------ |
| 1 | Alam | 10 | 10300 |
| 2 | Srijeeta | 20 | 6220 |
| 3 | Bhaskar | 30 | 11320 |
| 4 | Emely | 10 | 20500 |
| 5 | Freddy | 30 | 11320 |
### DEPT
| deptno | dname | dhead |
| ------ | ---------- | ------ |
| 10 | Sales | Ritika |
| 20 | HR | Ankit |
| 30 | Production | Abuzar |
| 40 | IT | Mesha |
---
## 🔗 JOINING TABLES
---
### 🌐 Unrestricted Join (Cartesian Product)
* Combines **every row of first table** with **every row of second table**
* Output rows = rows of table1 × rows of table2
```sql
SELECT empno, ename, dname
FROM emp, dept;
```
**Output (Partial):**
| empno | ename | dname |
| ----- | ----- | ---------- |
| 1 | Alam | Sales |
| 1 | Alam | HR |
| 1 | Alam | Production |
| ... | ... | ... |
📌 **No WHERE clause → Cartesian Product**
---
### 🔐 Restricted Join
* Uses a condition to match related rows
```sql
SELECT empno, ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;
```
**Output:**
| empno | ename | dname |
| ----- | -------- | ---------- |
| 1 | Alam | Sales |
| 2 | Srijeeta | HR |
| 3 | Bhaskar | Production |
| 4 | Emely | Sales |
| 5 | Freddy | Production |
---
### 🏷️ Using Table Aliases
* Alias = short name for table
```sql
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;
```
**Output:**
| ename | dname |
| -------- | ---------- |
| Alam | Sales |
| Srijeeta | HR |
| Bhaskar | Production |
| Emely | Sales |
| Freddy | Production |
---
### ➕ Additional Search Conditions in Joins
```sql
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.dname = 'HR';
```
**Output:**
| ename | dname |
| -------- | ----- |
| Srijeeta | HR |
---
### ⚖️ Equi Join
* Join using **equality (=)** operator
* Common column appears **twice**
```sql
SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno;
```
**Output (Partial):**
| empno | ename | deptno | salary | deptno | dname |
| ----- | -------- | ------ | ------ | ------ | ----- |
| 1 | Alam | 10 | 10300 | 10 | Sales |
| 2 | Srijeeta | 20 | 6220 | 20 | HR |
📌 **Exam Point:** Common column appears twice because of `SELECT *`
---
### 🚫 Non-Equi Join
* Uses operators like `>`, `<`, `>=`, `<=`
```sql
SELECT e.ename, e.salary
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.salary > 15000;
```
**Output:**
| ename | salary |
| ----- | ------ |
| Emely | 20500 |
---
### 🌿 Natural Join
* Automatically joins tables using **same column name**
* Common column appears **only once**
```sql
SELECT ename, salary, dname
FROM emp NATURAL JOIN dept;
```
**Output:**
| ename | salary | dname |
| -------- | ------ | ---------- |
| Alam | 10300 | Sales |
| Srijeeta | 6220 | HR |
| Bhaskar | 11320 | Production |
| Emely | 20500 | Sales |
| Freddy | 11320 | Production |
---
### 🧠 JOIN Clause (Modern SQL)
```sql
SELECT ename, dname
FROM emp JOIN dept
ON emp.deptno = dept.deptno;
```
**Output:**
| ename | dname |
| -------- | ---------- |
| Alam | Sales |
| Srijeeta | HR |
| Bhaskar | Production |
| Emely | Sales |
| Freddy | Production |
---
## 🧮 SET OPERATIONS
📌 **Rules (Very Important):**
* Same number of columns
* Same data type
* Same order
---
### 🔗 UNION
* Combines results and removes duplicates
```sql
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;
```
**Output:**
| deptno |
| ------ |
| 10 |
| 20 |
| 30 |
| 40 |
---
### ➖ MINUS
* Records in first query but not in second
```sql
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
```
**Output:**
| deptno |
| ------ |
| 40 |
---
### ✂️ INTERSECT
* Common records in both queries
```sql
SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept;
```
**Output:**
| deptno |
| ------ |
| 10 |
| 20 |
| 30 |
---
## 📝 ONE-LOOK REVISION TABLE
| Concept | Key Idea |
| --------------- | -------------------- |
| Cartesian Join | No WHERE clause |
| Restricted Join | Matching condition |
| Equi Join | `=` operator |
| Natural Join | Same column name |
| UNION | Merge, no duplicates |
| MINUS | Difference |
| INTERSECT | Common records |
---
Back to List
Calculating...