What is the difference between INNER JOIN and OUTER JOIN in MySQL?

In MySQL, JOIN is used to combine rows from two or more tables based on a related column between them. INNER JOIN and OUTER JOIN are two types of JOIN used in MySQL.

* INNER JOIN: It returns only those rows for which the join condition is true. In other words, it returns the intersection between the two tables.

The syntax for INNER JOIN is as follows:

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

Here, table1 and table2 are the names of the tables being joined, and column is the name of the column on which the tables are being joined.

For example, consider two tables ‘employees‘ and ‘departments‘ with the following data:

employees table:

id | name   | department_id
---|--------|--------------
1  | Alice  | 1
2  | Bob    | 2
3  | Charlie| 1
4  | David  | 3

departments table:

id | name    
---|--------
1  | Sales  
2  | Marketing

To join these two tables with INNER JOIN based on the ‘department_id‘ column, the query would be:

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

This would return the following result:

name    | name    
--------|---------
Alice   | Sales  
Charlie | Sales  
Bob     | Marketing

* OUTER JOIN: It returns all the rows from at least one table, and the matching rows from the other table. In other words, it returns the union of the two tables. There are three types of OUTER JOIN: LEFT OUTER JOIN (or LEFT JOIN), RIGHT OUTER JOIN (or RIGHT JOIN), and FULL OUTER JOIN (or FULL JOIN).

The syntax for LEFT OUTER JOIN is as follows:

SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column;

Here, table1 and table2 are the names of the tables being joined, and column is the name of the column on which the tables are being joined.

For example, to perform a LEFT OUTER JOIN between ‘employees‘ and ‘departments‘ tables based on the ‘department_id‘ column, the query would be:

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

This would return the following result:

name    | name    
--------|---------
Alice   | Sales  
Bob     | Marketing
Charlie | Sales  
David   | NULL    

In the result, the rows with NULL value in the ‘name‘ column are the rows from the ‘employees‘ table that did not match any row in the ‘departments‘ table based on the join condition.

Similar to LEFT OUTER JOIN, we can use RIGHT OUTER JOIN to return all the rows from the right table, and the matching rows from the left table. And, we can use FULL OUTER JOIN to return all the rows from both tables, and the matching rows based on the join condition.

In summary, INNER JOIN returns only the matching rows between two tables, while OUTER JOIN returns all the rows from at least one table, and the matching rows from the other table.