Using MySQL Left Join

One of the useful clauses of MySQL is JOIN. It is used to retrieve data from two or more related tables. MySQL contains mainly two types of JOIN. One is INNER JOIN and another is OUTER JOIN. The OUTER JOIN is again divided into LEFT JOIN and RIGHT JOIN. This tutorial is mainly focused on the uses of LEFT JOIN. The LEFT JOIN is used to retrieve all records of the left-side table based on the specific conditions and those records from the right-side table where the JOIN fields of both tables match. This tutorial shows the uses of LEFT JOIN in MySQL by using various examples.

Syntax:

SELECT field1, field2, field3, … fieldn
FROM table1
LEFT [OUTER] JOIN table2
ON table1.field = table2.field;

Here, using the OUTER keyword is optional. Any field of table1 and the common fields of both table1 and table2 can be defined in the select query. The records will be returned based on the conditions defined after the ON clause.

Prerequisite:

Before starting this tutorial, you have to create the necessary database and tables with data, to check the use of LEFT JOIN. Here, a database named company is created and two related tables named customers and orders are created. LEFT JOIN will be applied in these tables.

If you didn’t create the company database before then run the following statement to create the database.

CREATE DATABASE company;

Run the following statement to create customers table of four fields ( id, name, mobile_no, and email. Here, id is a primary key.

CREATE TABLE customers (
id INT(5) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
mobile_no VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL) ENGINE = INNODB;

Run the following statement to create orders table which is related to customers table of five fields ( id, order_date, customer_id, delivery_address and amount). Here id is a primary key and customer_id is a foreign key.

CREATE TABLE orders (
id VARCHAR(20) PRIMARY KEY,
order_date date,
customer_id INT(5) NOT NULL,
delivery_address VARCHAR(50) NOT NULL,
amount INT(11),
FOREIGN KEY (customer_id) REFERENCES customers(id))
ENGINE = INNODB;

Run the following statement to insert some data into customers table.

INSERT INTO customers values

(NULL,'Johnathan', '18477366643' , 'john@gmail.com'),
(NULL,'Musfiqur Rahman', '17839394985','musfiq@gmail.com'),
(NULL,'Jimmy','14993774655' , 'jimmy@gmail.com');

Run the following statement to insert some data into orders table.

INSERT INTO orders values
('1937747', '2020-01-02', 1, 'New Work', 1000),
('8633664', '2020-02-12', 3, 'Texas', 1500),
('4562777', '2020-02-05', 1, 'California', 800),
('3434959', '2020-03-01' , 2 , 'New Work', 900),
('7887775', '2020-03-17', 3 , 'Texas', 400);

The following statement will show the records of customers table.

SELECT * FROM customers;

The following statement will show the records of orders table.

SELECT * FROM orders;

Now, the tables with data are ready and you can apply LEFT JOIN on these tables to know how it works.

Use of simple LEFT JOIN

The following example shows the very simple use of LEFT JOIN. It will retrieve three fields from customers table and two fields from orders table where id of customers table and customer_id of orders table are equals.

SELECT customers.id, customers.name, customers.mobile_no, orders.order_date,
orders.amount

FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

The following output will appear after running the above SQL statement. 3 id values of customers table have appeared 5 times as customer_id values in orders table. So, five rows are returned as output.

Use of LEFT JOIN with WHERE clause in the LEFT table

The following statement shows how the WHERE clause can be used with JOIN between two tables. Here, 1 field from customers table and 3 fields of orders table will be retrieved where id of customers table and customer_id of orders table are equals and id of customers table is less than 3.

SELECT customers.name, orders.order_date, orders.delivery_address, orders.amount
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id where customers.id < 3;

2 records exist in customers table where id is less than three and 3 records of orders table match with these 2 records ( 1 and 2). So, three matching rows will be returned. The following output will appear after running the script.

Use of LEFT JOIN with WHERE clause in the right table

In the following SQL statement, orders table is used as a left-side table and customers table is used as a right-side of the LEFT JOIN operator. It will retrieve three fields from orders table and one field from customers table where customer_id of orders table and id of customers table are the same and ordered amount is greater than 900.

SELECT orders.id, orders.order_date, orders.amount, customers.name
FROM orders
LEFT JOIN customers
ON orders.customer_id = customers.id where orders.amount > 900;

If you check the orders table then you will see there are only two amount more than 900. These are 1000 and 1500 and the ordered customer ids are 1 and 3 which are the id values of Johnathan and Jimmy. The following output will appear after running the statement.

Any aggregate function can be used with the JOIN clause in the SQL statement. In the following SQL statement, LEFT JOIN is used in two tables and the aggregate function SUM() is used to calculate the total sum of ordered amounts group by id of customers table.

SELECT customers.name, customers.mobile_no, SUM(orders.amount)
FROM orders
LEFT JOIN customers
ON customers.id = orders.customer_id GROUP BY orders.customer_id;

There are three id values in customers table and according to orders table, there are two entries for the id value 1 (1000 + 800 = 1800), one entries for the id value 2 (900) and two entries for the id value 3 (400 + 1500 = 1900). The following output will appear after running the statement.

Conclusion:

According to the search requirements, you can use different types of JOIN clauses in your SQL statement to find out the accurate result from the database tables. The SQL statements used in this article explain the various uses of the LEFT JOIN to retrieve the different result set from two tables. You can use more than two tables to JOIN to write a more complex query. I hope, this tutorial will help the beginners to know the use of LEFT JOIN in MySQL.

from Linux Hint https://ift.tt/2Rs9UMv