What are different types of table joins in MySQL?

JOIN is a term used to co-relate or combine two things related to each other, similarly in MySQL when two or more tables are co-related to each other with identifiers we use JOIN clause to retrieve records from them. 

There are mainly three types of MySQL joins:

  1. MySQL INNER JOIN, also known as SIMPLE JOIN.
  2. MySQL LEFT OUTER JOIN, also known as LEFT JOIN.
  3. MySQL RIGHT OUTER JOIN, also known as RIGHT JOIN.

We have 2 tables employee and employee_reimbursement with a relationship of one to many between them. Below are the tables how they look like:

Now, let us discuss different joins one by one with some visual representation and database queries.

1. MySQL INNER JOIN (SIMPLE JOIN)

This is widely and commanly used JOIN where two or more tables are joined to fetch records which have common identifier. This join will fetch records from all tables where identifier value exactly matches in all tables.

Representational View

The MySQL INNER JOIN would return the records where table1 and table2 intersect as shown in image above.

Query Example

SELECT 
	* 
FROM 
	employee 
INNER JOIN 
	employee_reimbursement 
ON 
	employee.id = employee_reimbursement.emp_id;

Above query will return all records from both tables where employee id is matching in both employee and employee_reimbursement.

Output:

 

2. MySQL LEFT OUTER JOIN (LEFT JOIN)

This join will fetch records from a table where identifier value exactly matches in both tables and also all remaining records from table on left side of JOIN clause.

Representational View

The MySQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

Query Example

SELECT 
	* 
FROM 
	employee 
LEFT OUTER JOIN 
	employee_reimbursement 
ON 
	employee.id = employee_reimbursement.emp_id;

Above query will return all records from employee table and records from employee_reimbursement where employee id is matchingAnd records which does not have value in employee_reimbursement table will have null values as we can see in output below.

Output:

 

3. MySQL RIGHT OUTER JOIN (RIGHT JOIN)

This join will fetch records from a table where identifier value exactly matches in both tables and also all remaining records from table on right side of JOIN clause.

Representational View

The MySQL RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

Query Example

SELECT 
	* 
FROM 
	employee 
RIGHT OUTER JOIN 
	employee_reimbursement 
ON 
	employee.id = employee_reimbursement.emp_id;

Above query will return all records from employee_reimbursement table and records from employee where employee id is matchingAnd records which does not have value in employee table will have null values as we can see in output below.

Output:

 

SQL