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.
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.
Run the following statement to create customers table of four fields ( id, name, mobile_no, and email. Here, id is a primary key.
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.
Run the following statement to insert some data into customers table.
INSERT INTO customers values
Run the following statement to insert some data into orders table.
The following statement will show the records of customers table.
The following statement will show the records of orders table.
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.
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.
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.
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.
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.
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.