INNER JOIN and LEFT JOIN

In SQL, `INNER JOIN` and `LEFT JOIN` are two types of JOIN operations used to combine rows from two or more tables based on a related column between them. The main difference between them lies in how they handle rows that do not have matching values in the columns being joined.

1. INNER JOIN

   - The `INNER JOIN` keyword selects records that have matching values in both tables.

   - If there is no match for a row in one table, that row is not included in the result set.

   - It returns only the rows where there is a match between the specified columns in both tables.

   Example:

   SELECT employees.employee_id, employees.employee_name, departments.department_name

   FROM employees

   INNER JOIN departments ON employees.department_id = departments.department_id;

2. LEFT JOIN (or LEFT OUTER JOIN)

   - The `LEFT JOIN` keyword returns all records from the left table (the table specified before the JOIN keyword), and the matched records from the right table.

   - If there is no match for a row in the right table, NULL values are returned for columns from the right table.

   - It ensures that all rows from the left table are included in the result set, regardless of whether there is a match in the right table.

   Example:

   SELECT employees.employee_id, employees.employee_name, departments.department_name

   FROM employees

   LEFT JOIN departments ON employees.department_id = departments.department_id;

Comparison:

- Use `INNER JOIN` when you want to retrieve only the rows with matching values in both tables.

- Use `LEFT JOIN` when you want to retrieve all rows from the left table, and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

It's important to choose the appropriate type of join based on your specific use case and the data you want to retrieve. The choice between `INNER JOIN` and `LEFT JOIN` depends on whether you want to include only matching rows or include all rows from the left table regardless of matches in the right table.