SQL Joins

 SQL joins are used to combine rows from two or more tables based on a related column between them. There are several types of joins, each serving a specific purpose.

Types of SQL Joins

1. INNER JOIN:

   • Retrieves records that have matching values in both tables.
   • Syntax:

     SELECT columns
     FROM table1
     INNER JOIN table2
     ON table1.column = table2.column;
     

2. LEFT JOIN (LEFT OUTER JOIN):

   • Returns all records from the left table, and the matched records from the right table. If there's no match, the result is NULL on the right side.
   • Syntax:

     SELECT columns
     FROM table1
     LEFT JOIN table2
     ON table1.column = table2.column;
     

3. RIGHT JOIN (RIGHT OUTER JOIN):

   • Returns all records from the right table, and the matched records from the left table. If there's no match, the result is NULL on the left side.
   • Syntax:

     SELECT columns
     FROM table1
     RIGHT JOIN table2
     ON table1.column = table2.column;
     

4. FULL JOIN (FULL OUTER JOIN):

   • Combines the result of both LEFT JOIN and RIGHT JOIN. Returns all records from both tables, with NULLs where there is no match.
   • Syntax:

     SELECT columns
     FROM table1
     FULL JOIN table2
     ON table1.column = table2.column;
     

5. CROSS JOIN:

   • Returns the Cartesian product of both tables, meaning each row in the first table is paired with every row in the second table.
   • Syntax:

     SELECT columns
     FROM table1
     CROSS JOIN table2;
     

6. SELF JOIN:

   • A join where a table is joined with itself. It can be useful for hierarchical or comparative data.
   • Syntax:

     SELECT a.columns, b.columns
     FROM table a, table b
     WHERE a.column = b.column;
     

---

Example Tables

Table: Employees

EmployeeID	Name	DepartmentID
1	Alice	10
2	Bob	20
3	Charlie	NULL

Table: Departments

DepartmentID	DepartmentName
10	HR
20	IT
30	Finance

---

Examples of Joins

1. INNER JOIN Example:

   SELECT Employees.Name, Departments.DepartmentName
   FROM Employees
   INNER JOIN Departments
   ON Employees.DepartmentID = Departments.DepartmentID;
   

   Result:

   Name	DepartmentName
   Alice	HR
   Bob	IT

2. LEFT JOIN Example:

   SELECT Employees.Name, Departments.DepartmentName
   FROM Employees
   LEFT JOIN Departments
   ON Employees.DepartmentID = Departments.DepartmentID;
   

   Result:

   Name	DepartmentName
   Alice	HR
   Bob	IT
   Charlie	NULL

3. RIGHT JOIN Example:

   SELECT Employees.Name, Departments.DepartmentName
   FROM Employees
   RIGHT JOIN Departments
   ON Employees.DepartmentID = Departments.DepartmentID;
   

   Result:

   Name	DepartmentName
   Alice	HR
   Bob	IT
   NULL	Finance

4. FULL JOIN Example:

   SELECT Employees.Name, Departments.DepartmentName
   FROM Employees
   FULL JOIN Departments
   ON Employees.DepartmentID = Departments.DepartmentID;
   

   Result:

   Name	DepartmentName
   Alice	HR
   Bob	IT
   Charlie	NULL
   NULL	Finance