Inner join, left outer join, right outer join

Dear visitors, I hope you will be fine and hope everything is going well. Today I’ll talk about MySql “Join”. What is join? how does it work? What is the syntax? and Why should we use it? We will discuss these all with some examples. Our main goal is catching data from multiple tables using join. But first, we assume a problem and then we’ll talk on it. So let’s start our coding journey.

Table of Contents

The problem regarding fetching date from multiple tables

As we discussed earlier that we will discuss a problem regarding fetching data from multiple tables and then we will show all the data in one list. So let’s start with a problem. Suppose that we have a POS software that stores dealers data along with their orders. Now our goal is to perform different queries to fetch different types of results. Like fetching orders that match dealers ID. Fetching orders data that matches orders ID and so on. This can be done via PHP but it is a headache and will make your query slow down. Therefore, we will use “JOIN” here.

The Solution to the Problem

As you understood the problem so now we’ll talk about its solutions. As discussed earlier that we’ll use JOIN but first we should know about JOIN that what is join? and how can we use it?

Syntax of JOIN keyword in MySQL

Actually MySQL JOINS are used to retrieve data from multiple tables. A MySQL JOIN is performed whenever you need to fetch data from two or more tables in a SQL SELECT statement.

There are different types of MySQL joins:

  • INNER JOIN (or called a simple join)
  • LEFT OUTER JOIN (or LEFT JOIN)
  • RIGHT OUTER JOIN (or RIGHT JOIN)

INNER JOIN (Simple Join)

INNER JOIN is the common type of join. MySQL INNER JOINS returns all rows from multiple tables where the condition is met.

Syntax

The syntax for the INNER JOIN in MySQL is:

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

Here is pictorial explanation

mysql-inner_join
MySQL INNER JOIN

Example of INNER JOIN

For a better understanding of INNER JOIN, take a look at these two tables. The first table that we have is called dealers with two fields (dealers_id and dealer_name). It contains the following data:

dealers_iddealer_name
900NETSole Pvt. Ltd.
901Neon Zone
9022BNET
903Waylink Pvt. Ltd.
“dealers” Table

Our another table is called orders with three fields (order_id, supplier_id, and order_date). It contains the following data of orders made by dealers:

order_iddealer_idorder_date
5001259002020/05/12
5001269012020/05/13
5001279102020/05/14
“orders” Table

Now use the below query:

SELECT dealers.dealers_id, dealers.dealer_name, orders.order_date
FROM dealers 
INNER JOIN orders
ON dealers.dealers_id = orders.dealer_id;

Above mentioned MySQL INNER JOIN example query would return all rows from the dealers and orders tables where there is a matching dealer_id value in both the dealers and orders tables. Result would be look like this:

dealer_idnameorder_date
900NETSole Pvt. Ltd.2020/05/12
901Neon Zone2020/05/13
“orders” Table

As you can see that only two rows are returned because dealer_id 910 not exists in the orders table.

Now let’s move to LEFT OUTER JOIN.

LEFT OR LEFT OUTER JOIN

Here is another type of join that is called a LEFT or LEFT OUTER JOIN. This will return all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax of LEFT OUTER JOIN

Below is the syntax for the LEFT OUTER JOIN in MySQL is. Look and try to understand then we will go ahead.

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

Here is a picture that will clear a little bit more about the LEFT OUTER JOIN.

left outer join in mysql
Left Outer Join Image

In the image above it clearly shows that the LEFT JOIN would return all records from table1 and only those records from table2 that intersect with table1.

LEFT OUTER JOIN Example Query

Let’s try on a real query that will do the magic. But you have to generate two tables first. Our first table would be a “dealers” table. Here it is:

dealers_iddealer_name
900NETSole Pvt. Ltd.
901Neon Zone
9022BNET
903Waylink Pvt. Ltd.
“dealers” Table

Our second table is “orders” table. See below:

order_iddealers_idorder_date
9999002020/05/12
10009012020/05/13
“orders” table

Now take look at magic spell.

SELECT dealers.dealers_id, dealers.dealer_name, orders.order_date
FROM dealers
LEFT JOIN orders
ON dealers.dealers_id = orders.dealer_id;

Explanation of LEFT JOIN

In the above LEFT JOIN example query, we will get all rows from the dealer’s table and only those rows from the orders table where the joined fields are equal.

If a “dealers_id” value in the “dealers” table does not exist in the “orders” table, all fields in the orders table will display as null in the result set. Finally, we’ll get this list

dealers_iddealer_nameorder_date
900NETSole Pvt. Ltd.2020/05/12
901Neon Zone2020/05/13
9022BNETnull
903Waylink Pvt. Ltd.null
“orders” table

Hay….. we did. What’s next now? Now we will move to RIGHT OUTER JOIN for fetching data from multiple tables

About RIGHT OUTER JOIN

At last, at the bottom of the JOIN family, there is RIGHT OUTER JOIN or simply RIGHT JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the RIGHT OUTER JOIN in MySQL is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

Here is picture for understanding RIGHT JOIN.

right outer join in mysql
Right Outer Join

RIGHT OUTER JOIN Example Query

Before we dive into the query, we first see two tables as we did earlier to understand the query. We have two tables. One is “dealers” and the second is “orders”. First, we will take the “dealers” table.

dealers_iddealer_name
900NETSole Pvt. Ltd.
901Neon Zone
“dealers” table

and next we have a called “orders” with three fields with some data. See below:

order_iddealer_idorder_date
9999002020/05/12
10009012020/05/13
10019022020/05/14
“orders” table

Now run the following query to see the result:

SELECT orders.order_id, orders.order_date, dealers.dealer_name
FROM dealers
RIGHT JOIN orders
ON dealers.dealers_id = orders.dealer_id;

Above query will return following list

order_idorder_datedealer_name
9992020/05/12NETSole Pvt. Ltd.
10002020/05/13Neon Zone
10012020/05/14null
“orders” table

Explanation of RIGHT OUTER Query

As you can see above table and SQL query clearly describe that RIGHT JOIN query returns all rows from the “orders” table and only those rows from the “dealers” table where the joined fields are equal.

If a “dealer_id” value in the “orders” table does not exist in the “dealers” table, all fields in the “dealers” table will display as null in the result set.

Bye Bye

At last, we reached our endpoint and completed our journey regarding fetching data from multiple tables. Now you are free to leave this page if you want. However, there is something more to read. If you are not tired and start a new journey then pick a tutorial and good luck.

Leave a Reply - I will show after approval