Types of Joins in SQL

In SQL, INNER JOIN, LEFT JOIN, and RIGHT JOIN are different types of joins used to retrieve data from multiple tables based on a related column between them. Here’s a breakdown of each type and their differences:

1. INNER JOIN:

• Definition: An INNER JOIN returns only the rows that have matching values in both tables.
• Usage: It is used when you want to retrieve rows that have common values in the columns specified.
• Example:

  SELECT *
  FROM TableA
  INNER JOIN TableB
  ON TableA.common_column = TableB.common_column;
  

2. LEFT JOIN (or LEFT OUTER JOIN):

• Definition: A LEFT JOIN returns all the rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.
• Usage: It is used when you want to include all records from the left table regardless of whether there is a match in the right table.
• Example:

  SELECT *
  FROM TableA
  LEFT JOIN TableB
  ON TableA.common_column = TableB.common_column;
  

3. RIGHT JOIN (or RIGHT OUTER JOIN):

• Definition: A RIGHT JOIN returns all the rows from the right table, and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.
• Usage: It is used when you want to include all records from the right table regardless of whether there is a match in the left table.
• Example:

  SELECT *
  FROM TableA
  RIGHT JOIN TableB
  ON TableA.common_column = TableB.common_column;
  

Summary of Differences:

• INNER JOIN: Retrieves only the matching rows from both tables.
• LEFT JOIN: Retrieves all rows from the left table, and the matching rows from the right table (or NULL if no match).
• RIGHT JOIN: Retrieves all rows from the right table, and the matching rows from the left table (or NULL if no match).

Example Scenario:

Imagine two tables: Employees and Departments.

• INNER JOIN:

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

  This will return only employees who belong to a department listed in the Departments table.

• LEFT JOIN:

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

  This will return all employees, including those who do not belong to any department (with NULL in DepartmentName).

• RIGHT JOIN:

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

  This will return all departments, including those with no employees assigned (with NULL in Name).

I hope this helps you understand the differences between these types of joins!