Unit IV JOIN OPERATORS

 Join Operators: Cross Join – Natural Join – Join USING Clause – JOIN ON Clause – Outer Join. Explain the above topic with definition and example table

Here's a clear explanation of the SQL Join Operators with definitions and examples using simple tables:

---

🔗 1. Cross Join

Definition:

• A Cross Join returns the Cartesian product of two tables.

• That means every row from the first table is combined with every row from the second table.

• It doesn't require any condition.

Example:

Table A – Employees

EmpID	Name
1	John
2	Alice

Table B – Departments

DeptID	DeptName
10	HR
20	IT

📌 Syntax

SELECT * 
FROM table1
CROSS JOIN table2;

Query:

SELECT * FROM Employees CROSS JOIN Departments;

Result:

EmpID	Name	DeptID	DeptName
1	John	10	HR
1	John	20	IT
2	Alice	10	HR
2	Alice	20	IT

---

🔗 2. Natural Join

Definition:

• A Natural Join automatically joins tables based on all columns with the same names and compatible data types in both tables.

Example:

Table A – Employees

EmpID	Name	DeptID
1	John	10
2	Alice	20

Table B – Departments

DeptID	DeptName
10	HR
20	IT

Query:

SELECT * FROM Employees NATURAL JOIN Departments;

Result:

EmpID	Name	DeptID	DeptName
1	John	10	HR
2	Alice	20	IT

---

🔗 3. Join USING Clause

Definition:

• JOIN ... USING(column_name) joins tables using a specific common column.

• This is more explicit than Natural Join.

Example:

SELECT * FROM Employees JOIN Departments USING (DeptID);

Same Result as Natural Join:

EmpID	Name	DeptID	DeptName
1	John	10	HR
2	Alice	20	IT

---

🔗 4. JOIN ON Clause

Definition:

• JOIN ... ON allows joining tables using any condition, even if the column names are different.

• It's the most flexible and commonly used join.

Example:

SELECT * 
FROM Employees E 
JOIN Departments D 
ON E.DeptID = D.DeptID;

Result:

EmpID	Name	DeptID	DeptName
1	John	10	HR
2	Alice	20	IT

---

🔗 5. Outer Join

Definition:

• Outer Join returns all records from one or both tables, even if there is no match.

  • LEFT OUTER JOIN: All records from the left table and matched records from the right.

  • RIGHT OUTER JOIN: All records from the right table and matched records from the left.

  • FULL OUTER JOIN: All records when there is a match in either table.

Example (LEFT OUTER JOIN):

Assume we add another employee without a department:

Employees

EmpID	Name	DeptID
1	John	10
2	Alice	20
3	Mark	NULL

Query:

SELECT * FROM Employees LEFT OUTER JOIN Departments ON Employees.DeptID = Departments.DeptID;

Result:

EmpID	Name	DeptID	DeptName
1	John	10	HR
2	Alice	20	IT
3	Mark	NULL	NULL

---

Let me know if you'd like a visual diagram or want to practice queries!