Blog Posts

MySQL Join | Types and Examples

Blog: Think Data Analytics Blog

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

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.

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.

The post MySQL Join | Types and Examples appeared first on Big Data, Data Analytics, IOT, Software Testing, Blockchain, Data Lake – Submit Your Guest Post.

Leave a Comment

Get the BPI Web Feed

Using the HTML code below, you can display this Business Process Incubator page content with the current filter and sorting inside your web site for FREE.

Copy/Paste this code in your website html code:

<iframe src="https://www.businessprocessincubator.com/content/%EF%BF%BCmysql-join-types-and-examples/?feed=html" frameborder="0" scrolling="auto" width="100%" height="700">

Customizing your BPI Web Feed

You can click on the Get the BPI Web Feed link on any of our page to create the best possible feed for your site. Here are a few tips to customize your BPI Web Feed.

Customizing the Content Filter
On any page, you can add filter criteria using the MORE FILTERS interface:

Customizing the Content Filter

Customizing the Content Sorting
Clicking on the sorting options will also change the way your BPI Web Feed will be ordered on your site:

Get the BPI Web Feed

Some integration examples

BPMN.org

XPDL.org

×