SQL

MySQL Join | Types and Examples

Pinterest LinkedIn Tumblr

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

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:

supplier_idsupplier_name
10000IBM
10001Hewlett Packard
10002Microsoft
10003NVIDIA

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

order_idsupplier_idorder_date
500125100002013/05/12
500126100012013/05/13
500127100042013/05/14

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:

supplier_idnameorder_date
10000IBM2013/05/12
10001Hewlett Packard2013/05/13

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:

supplier_idsupplier_name
10000IBM
10001Hewlett Packard
10002Microsoft
10003NVIDIA

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

order_idsupplier_idorder_date
500125100002013/05/12
500126100012013/05/13

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:

supplier_idsupplier_nameorder_date
10000IBM2013/05/12
10001Hewlett Packard2013/05/13
10002Microsoft<null>
10003NVIDIA<null>

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:

supplier_idsupplier_name
10000Apple
10001Google

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

order_idsupplier_idorder_date
500125100002013/08/12
500126100012013/08/13
500127100022013/08/14

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:

order_idorder_datesupplier_name
5001252013/08/12Apple
5001262013/08/13Google
5001272013/08/14<null>

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.

Also Read – MySQL Locks

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.