JOIN’S

1. INNER JOIN

• Returns records that have matching values in both tables.
• Only common data between both tables.

Example:

sqlCopyEditSELECT employees.name, employees.salary, department.dept_name
FROM employees
INNER JOIN department ON employees.dept_id = department.dept_id;

Output:

name	salary	dept_name
John	50000	IT
Sarah	60000	HR

---

💡 2. LEFT JOIN (LEFT OUTER JOIN)

• Returns all records from the left table, and the matched records from the right table.
• If no match, NULL values will appear from the right table.

Example:

sqlCopyEditSELECT employees.name, employees.salary, department.dept_name
FROM employees
LEFT JOIN department ON employees.dept_id = department.dept_id;

Output:

name	salary	dept_name
John	50000	IT
Sarah	60000	HR
Mike	40000	NULL

---

💡 3. RIGHT JOIN (RIGHT OUTER JOIN)

• Returns all records from the right table, and the matched records from the left table.
• If no match, NULL values will appear from the left table.

Example:

sqlCopyEditSELECT employees.name, employees.salary, department.dept_name
FROM employees
RIGHT JOIN department ON employees.dept_id = department.dept_id;

Output:

name	salary	dept_name
John	50000	IT
Sarah	60000	HR
NULL	NULL	Sales

---

💡 4. FULL OUTER JOIN

• Returns all records when there is a match in either left table or right table.
• If no match, NULL values will appear.

Example:

sqlCopyEditSELECT employees.name, employees.salary, department.dept_name
FROM employees
FULL OUTER JOIN department ON employees.dept_id = department.dept_id;

Output:

name	salary	dept_name
John	50000	IT
Sarah	60000	HR
Mike	40000	NULL
NULL	NULL	Sales

---

💡 5. CROSS JOIN

• Returns the Cartesian product of both tables.
• This means every row of the first table joins with every row of the second table.

Example:

sqlCopyEditSELECT employees.name, department.dept_name
FROM employees
CROSS JOIN department;

Output:

name	dept_name
John	IT
John	HR
Sarah	IT
Sarah	HR

---

💡 6. SELF JOIN

• Joins a table with itself.
• Useful for comparing values in the same table.

Example:

sqlCopyEditSELECT A.name AS Employee, B.name AS Manager
FROM employees A, employees B
WHERE A.manager_id = B.emp_id;

Output:

Employee	Manager
John	Sarah
Mike	John

---

✅ Summary

JOIN Type	Description
INNER JOIN	Returns matching records from both tables
LEFT JOIN	Returns all records from the left table + matched from the right table
RIGHT JOIN	Returns all records from the right table + matched from the left table
FULL JOIN	Returns all records from both tables
CROSS JOIN	Returns cartesian product of both tables
SELF JOIN	Joins table with itself