MySQL Join | Types and Examples

Mahendra — Tue, 26 Apr 2022 13:22:41 +0000

This blog talks about the MySQL Joins and different types of MySQL Join with examples.

What is MySQL Join?
Types of MySQL Joins 2.1 Inner Join 2.2 Left Outer Join 2.3 Right Outer Join
Conclusion

What is MySQL JOIN?

MySQL databases typically hold a lot of information. DBAs and analysts must select records from two or more tables based on certain conditions in order to appropriately examine this data. MySQL Join come in handy in this situation. Joins allow you to combine data from multiple tables into a single query. The tables must be linked together with a common key value in order for SQL Joins to work.

One of the most commonly used MySQL Server clauses for extracting and collating data from two or more tables is SQL Joins. Data is arranged in a huge array of tables in a real-world database, necessitating the requirement to combine these many databases based on logical relationships between them.

Types of MySQL Joins

There are three primary types of MySQL Joins:

Inner Join
Left Outer Join
Right Outer Join

Inner Join

You've probably created a statement that employs a MySQL INNER JOIN before. INNER JOINS in MySQL return all rows from many tables that meet the JOIN requirement.

Here is the syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Here is a visual representation to understand INNER JOINS:

The entries where table1 and table2 intersect would be returned by the MySQL INNER JOIN.

Given below is an example of MySQL INNER JOIN:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This MySQL INNER JOIN example would retrieve all rows from the suppliers and orders tables that have the same supplier id value.

Take a look at some numbers to see how INNER JOINS work:

“suppliers” is a table with two fields (supplier id and supplier name). It includes the following information:

“orders” is a separate table with three fields (order id, supplier id, and order date). It includes the following information:

Run the below code which contains the INNER JOIN statement:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

The result set for the above code will be:

Because the supplier ids 10002 and 10003 do not exist in both tables, the rows for Microsoft and NVIDIA from the supplier table would be removed. Because the supplier id 10004 does not exist in the “suppliers” table, the row for 500127 (order id) from the orders table would be removed.

Left Outer Join

A MySQL LEFT OUTER JOIN is another sort of join. All rows from the LEFT-hand table provided in the ON condition are returned, as well as only those rows from the other table where the linked fields are equivalent (join condition is met).

Here is the syntax:
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
The LEFT OUTER JOIN keywords are substituted with LEFT JOIN in several databases.

Here is a visual representation to understand LEFT JOINS:

The LEFT OUTER JOIN in MySQL would return all records from table 1 as well as only those from table 2 that intersect with table 1.

Given below is an example of LEFT OUTER JOIN:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This LEFT OUTER JOIN example would return all rows from the suppliers table and only those rows from the orders table with equal connected fields.

All fields in the orders table will display as null in the result set if a supplier id value in the suppliers table does not exist in the orders table.

Let's take a look at some numbers to see how LEFT OUTER JOINS work:

Suppliers is a table with two fields (supplier id and supplier name). It includes the following information:

There are three fields in the orders table (order id, supplier id, and order date). It includes the following information:

Run the below code which contains the LEFT OUTER JOIN statement:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

The result set would be:

Because a LEFT OUTER JOIN was utilized, the rows for Microsoft and NVIDIA would be included. You'll notice, though, that the order date field for those records has a null value.

Right Outer Join

A MySQL RIGHT OUTER JOIN is another type of join. All rows from the RIGHT-hand table provided in the ON condition are returned, as well as only those rows from the other table where the linked fields are equivalent.

Here is the syntax:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
Here is a visual representation of the RIGHT OUTER JOIN:

The MySQL RIGHT OUTER JOIN returns the shaded area in this visual diagram:

The MySQL RIGHT OUTER JOIN returns all records from table 2 as well as only those from table 1 that intersect with table 2.

Given below is an example of MySQL RIGHT OUTER JOIN:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This example of a RIGHT OUTER JOIN would return all rows from the orders table as well as only those rows from the suppliers table where the connected fields are equal.

All fields in the suppliers table will display as null in the result set if a supplier id value in the orders table does not exist in the suppliers table.

Let's take a look at some numbers to see how RIGHT OUTER JOINS work:

Suppliers is a table with two fields (supplier id and supplier name). It includes the following information:

There are three fields in the orders table (order id, supplier id, and order date). It includes the following information:

Run the below code which contains the ILEFT OUTER JOIN statement:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

The result set will be:

Because a RIGHT OUTER JOIN was performed, the record for 500127 (order id) would be included. You'll notice, though, that the supplier name field for that record has a value of null.

Conclusion

You have learned about the three key types of MySQL JOINS along with their respective syntaxes and examples for better understanding.

Hevo is a No-code Data Pipeline that is producing the industry's most robust and comprehensive ETL solution. At a reasonable price, you can link with your databases, cloud apps, flat files, clickstream, and other systems.

DEV Community: Mahendra

MySQL Join | Types and Examples

Table of Contents

What is MySQL JOIN?

Types of MySQL Joins

Inner Join

Conclusion