5.1 Joins: INNER, LEFT, RIGHT, FULL

In SQL, joins are used to combine rows from two or more tables based on a related column between them. They allow you to retrieve data from multiple tables in a single query, making it possible to create comprehensive reports and perform complex queries. The four main types of joins—INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN—have distinct behaviors and are used depending on the type of data you want to include in your result.

5.1.1 INNER JOIN - Matching Records Only

An INNER JOIN returns rows when there is a match in both tables. If there is no match, the row is excluded from the result. This is the most common type of join, used when you want to find rows where there is a direct relationship between the tables.

• Purpose: To retrieve records that have matching values in both tables.
• Syntax:

  SELECT column1, column2, column3
  FROM table1
  INNER JOIN table2
  ON table1.column_name = table2.column_name;
  

• Example:
  Suppose you have two tables: employees and departments. You want to find employees and their department names:

  SELECT employees.employee_name, departments.department_name
  FROM employees
  INNER JOIN departments
  ON employees.department_id = departments.department_id;
  

  This will return only those employees who have a matching department in the departments table.

5.1.2 LEFT JOIN (or LEFT OUTER JOIN) - Include All from Left Table

A LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left table and the matched records from the right table. If there is no match in the right table, the result will still include all rows from the left table, with NULL values for the right table's columns.

• Purpose: To include all rows from the left table, along with matching rows from the right table. If there is no match, the right table's columns will contain NULL values.
• Syntax:

  SELECT column1, column2, column3
  FROM table1
  LEFT JOIN table2
  ON table1.column_name = table2.column_name;
  

• Example:
  Retrieve all employees and their department names, even if some employees do not belong to a department:

  SELECT employees.employee_name, departments.department_name
  FROM employees
  LEFT JOIN departments
  ON employees.department_id = departments.department_id;
  

  This will return all employees, including those who do not have a department, with NULL in the department name for those employees.

5.1.3 RIGHT JOIN (or RIGHT OUTER JOIN) - Include All from Right Table

A RIGHT JOIN (or RIGHT OUTER JOIN) is similar to a LEFT JOIN, but it returns all records from the right table and the matched records from the left table. If there is no match in the left table, the result will still include all rows from the right table, with NULL values for the left table's columns.

• Purpose: To include all rows from the right table, along with matching rows from the left table. If there is no match, the left table's columns will contain NULL values.
• Syntax:

  SELECT column1, column2, column3
  FROM table1
  RIGHT JOIN table2
  ON table1.column_name = table2.column_name;
  

• Example:
  Retrieve all departments and their employees, even if some departments have no employees:

  SELECT employees.employee_name, departments.department_name
  FROM employees
  RIGHT JOIN departments
  ON employees.department_id = departments.department_id;
  

  This will return all departments, including those without employees, with NULL in the employee name for those departments.

5.1.4 FULL JOIN (or FULL OUTER JOIN) - Include All from Both Tables

A FULL JOIN (or FULL OUTER JOIN) returns all records when there is a match in either the left or the right table. If there is no match, the missing side will contain NULL values. This is useful when you want to include all records from both tables, whether they have a match or not.

• Purpose: To return all rows when there is a match in either the left or the right table. If there is no match, the missing side will have NULL values.
• Syntax:

  SELECT column1, column2, column3
  FROM table1
  FULL JOIN table2
  ON table1.column_name = table2.column_name;
  

• Example:
  Retrieve all employees and departments, even if some employees don't belong to a department and some departments don't have employees:

  SELECT employees.employee_name, departments.department_name
  FROM employees
  FULL JOIN departments
  ON employees.department_id = departments.department_id;
  

  This will return all employees and all departments. If an employee doesn't belong to a department, their department name will be NULL. If a department has no employees, the employee name will be NULL.

5.1.5 When to Use Each Join

• INNER JOIN: Use when you only want records that exist in both tables.
• LEFT JOIN: Use when you want all records from the left table and matched records from the right table. If there is no match, the result will still include rows from the left table with NULL in the columns from the right table.
• RIGHT JOIN: Use when you want all records from the right table and matched records from the left table. If there is no match, the result will include rows from the right table with NULL in the columns from the left table.
• FULL JOIN: Use when you want all records from both tables, with NULL values where there is no match.

5.1.6 Practical Example of Joins

1. INNER JOIN Example:
   Get a list of orders and their corresponding customer names:

   SELECT orders.order_id, customers.customer_name
   FROM orders
   INNER JOIN customers
   ON orders.customer_id = customers.customer_id;
   

   This query will return only those orders that have a matching customer.
2. LEFT JOIN Example:
   Get a list of all employees and the departments they belong to, including those employees who do not belong to any department:

   SELECT employees.employee_name, departments.department_name
   FROM employees
   LEFT JOIN departments
   ON employees.department_id = departments.department_id;
   

3. RIGHT JOIN Example:
   Get a list of all products and their suppliers, including suppliers that do not supply any products:

   SELECT products.product_name, suppliers.supplier_name
   FROM products
   RIGHT JOIN suppliers
   ON products.supplier_id = suppliers.supplier_id;
   

4. FULL JOIN Example:
   Get a list of all employees and all departments, whether the employees belong to a department or not:

   SELECT employees.employee_name, departments.department_name
   FROM employees
   FULL JOIN departments
   ON employees.department_id = departments.department_id;
   

5.1.7 Conclusion

SQL joins are essential tools for working with relational databases, allowing you to combine data from multiple tables based on relationships between them. Understanding the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN enables you to perform a wide range of data retrieval operations, ensuring you can fetch all the data you need in a single query.