Learn MySql Join Between Tables Practical Example
In this article, I am going to discuss the MySql Join Query. Today we will learn about MySql Join Query and why they are using it and how to use them. I am going to discuss this whole discussion is three-part. Today is one of the first parts. The whole thing I will cover in this article are:
MySql Join
- What are MySql Joins?
- Join (Inner)
- Left Join (Outer)
- Right Join (Outer)
- Full Join (Outer)
- Union
- Cross Join
- Self Join
- Conclusion
What are MySql Joins?
MySql JOIN’s are the most significant thing in RDBMS (Relational Database Management System). RDBMS is a relational database refers to a database that stores data in a structured format, using rows and columns. JOIN’s are working on different tables with the same value and property. Basically there are four types of join in MySQL to join two or more tables.
- Join (Inner Join)
- Left Join (Outer Join)
- Right Join (Outer Join)
- Full Join (Outer Join)
Using those four JOIN’s, we can customize another three join in more than one MySql tables. There are three simple approaches to join at least two tables:
- Union
- Cross Join
- Self Join
So let’s create a sample database structure with three table named “customer”, “customer_order” & “order_status”. The schema of our tables are:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- -- Table structure for table `customer` -- CREATE TABLE `customer` ( `id` int(11) NOT NULL, `firstname` varchar(32) NOT NULL, `lastname` varchar(32) NOT NULL, `email` varchar(96) NOT NULL, `telephone` varchar(32) NOT NULL, `approved` tinyint(1) NOT NULL, `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- -- Table structure for table `customer_order` -- CREATE TABLE `customer_order` ( `id` int(11) NOT NULL, `customer_id` int(11) NOT NULL DEFAULT '0', `payment_method` varchar(128) NOT NULL, `total` decimal(15,4) NOT NULL DEFAULT '0.0000', `order_status_id` int(11) NOT NULL DEFAULT '0', `currency_code` varchar(3) NOT NULL, `date_added` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
|
1 2 3 4 5 6 7 8 9 10 |
-- -- Table structure for table `order_status` -- CREATE TABLE `order_status` ( `id` int(11) NOT NULL, `name` varchar(32) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
We have 10 rows of customer details & 12 rows of order details. Check Sample database sql file from github
MySql Join (Inner)
Simply we can say that MySql Inner Join returns all the records that have matching values in both tables. You can add some conditions using where clause if you want in your MySql queries. In this article, you will learn Mysql INNER JOIN with practical examples.
Two tables named customer & customer_order in our sample database. You can see that in the customer_order table, there is no customer information in the customer_order table. Only the foreign_key named customer_id is available in this table. All the customer’s information has stored in the customer table. As a result, if you want to display order information on your website then you need both customer and order information. But, in this case, those are in two different tables. So you need to apply Inner Join. Here is the Inner Join query structure:
SELECT column_name(s)
FROM table1
JOIN table2 ON
ON table1.column_name = table2.column_name;
INNER JOIN and simple JOIN both are the same. You can write as like as you can.
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON
ON table1.column_name = table2.column_name;
In respect of our sample database the JOIN query will be:
|
1 2 3 4 5 6 7 |
SELECT CONCAT(customer.firstname, ' ', customer.lastname) as Name, customer_order.payment_method, customer_order.total as Amount, customer_order.currency_code, customer_order.date_added from customer INNER JOIN customer_order on customer.id = customer_order.customer_id |
Using this query we will get all the order information with customer details. The contact function used to join two string column in MySql as a customer name.
Here the name fields are getting from the customer table using MySql Inner Join.
MySql Left Join (Outer)
MySql Left Join returns all records from the left table and the matched records from the right table. The structure for Left Join is:
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON
ON table1.column_name = table2.column_name;
There are 10 rows of customer details in the customer table and 12 rows of order details in the customer_order table. Moreover, In this case, we consider as the customer table is the left table and the customer_order table is the right table. So the query is:
|
1 2 3 4 5 6 7 |
SELECT CONCAT(customer.firstname, ' ', customer.lastname) as Name, customer_order.payment_method, customer_order.total as Amount, customer_order.currency_code, customer_order.date_added from customer Left JOIN customer_order on customer.id = customer_order.customer_id |
In the Left Join resultset, there are 14 results. That means the query returns all the records of the left table (customer) and matched record from the right table (customer_order). Here I remind you that the left table column is the name and right table column are payment_method, amount, currency_code, date_added. The result is NULL from the right side if there is no match.
This is the end of today. I am gonna cover this discussion with the next two parts. I hope you enjoyed. Stay with me.
Thanks
2 Responses
[…] part-1 I have shared sample database structure for practicing MySql Join query. If you are not read it yet […]
[…] that I discussed with another previous two-part. Before continuing this final part you can check part-1 & part-2 of the whole Join discussion. MySql Join, Inner Join, Left Join are in […]