MySQL - Left Join

Unlike inner join, which provides the intersection values of two tables, there is another type of join called Outer Join. This outer join provides the collection of matched and unmatched records of two tables in multiple cases.

MySQL Left Join

Left Join is a type of outer join that retrieves all the records from the first table and matches them to the records in second table.

If the records in left table do not have their counterparts in the second table, NULL values are added.

But, if the number of records in first table is less than the number of records in second table, the records in second table that do not have any counterparts in the first table will be discarded from the result.

Syntax

Following is the basic syntax of Left Join in MySQL −

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example

Using the following query, let us create a table named CUSTOMERS, that contains the personal details of customers including their name, age, address and salary.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The table will be created as −

Let us create another table ORDERS, containing the details of orders made and the date they are made on.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

The table is displayed as follows −

Left Join Query:

Using the following left join query, we will retrieve the details of customers who made an order at the specified date. If there is no match found, the query below will return NULL in that record.

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

The joined result-set is obtained as −

Joining Multiple Tables with Left Join

Left Join also joins multiple tables where the first table is returned as a whole and the next tables are matched with the rows in the first table. If the records are not matched, NULL is returned.

Syntax

The syntax to join multiple tables using Left Join is given below −

SELECT column1, column2, column3...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name
LEFT JOIN table3
ON table2.column_name = table3.column_name
.
.
.

Example

To demonstrate Left Join with multiple tables, let us consider the previously created tables CUSTOMERS and ORDERS. In addition to these we will create another table named EMPLOYEE, which consists of the details of employees in an organization and sales made by them, using the following query −

CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);

Now, we can insert values into this empty tables using the INSERT statement as follows −

INSERT INTO EMPLOYEE VALUES
(102, 'SARIKA', 4500),
(100, 'ALEKHYA', 3623),
(101, 'REVATHI', 1291),
(103, 'VIVEK', 3426);

The table is created as −

Left Join Query:

Let us join these three tables using the left join query given below −

SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
LEFT JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

Output

The resultant table is obtained as follows −

Left Join with WHERE Clause

To filter the records after joining two tables, a WHERE clause can be applied.

Syntax

The syntax of Left Join when used with WHERE clause is given below −

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

Example

Records in the combined database tables can be filtered using the WHERE clause. Consider the previous two tables CUSTOMERS and ORDERS; and join them using the left join query by applying some constraints using the WHERE clause.

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

Output

The output is obtained as − −

Left Join Using a Client Program

We can also perform the left join operation on one or more tables using a client program.

Syntax

$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author';
$mysqli->query($sql);

sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b 
ON a.tutorial_author = b.tutorial_author";
con.query(sql);  

String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b 
ON a.tutorial_author = b.tutorial_author";
st.executeQuery(sql);

left_join_query = "SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUST_ID"
cursorObj.execute(left_join_query)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');

$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
			FROM tutorials_tbl a LEFT JOIN tcount_tbl b
			ON a.tutorial_author = b.tutorial_author';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    echo " following is the both table details after executing left join! \n";
    while ($row = $result->fetch_assoc()) {
        printf( "Id: %s, Author: %s, Count: %d", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"] );
        printf("\n");
    }
} else {
    printf('No record found.
');
}
mysqli_free_result($result);
$mysqli->close();

following is the both table details after executing left join!
Id: 1, Author: John Poul, Count: 0
Id: 2, Author: Abdul S, Count: 0
Id: 3, Author: Sanjay, Count: 1
Id: 101, Author: Aman kumar, Count: 0
Id: 102, Author: Sarika Singh, Count: 0    

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);
  //left join
  sql =
    "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[
   { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 2 },
   { tutorial_id: 2, tutorial_author: 'Abdul S', tutorial_count: null },
   {
     tutorial_id: 101,
     tutorial_author: 'Aman kumar',
     tutorial_count: null
   },
   {
     tutorial_id: 102,
     tutorial_author: 'Sarika Singh',
     tutorial_count: null
   }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class LeftJoin {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //MySQL LEFT JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after LEFT Join...!");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2)+" "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}       

Connected successfully...!
Table records after LEFT Join...!
1 John Paul 1
2 Abdul S null
3 Sanjay 1
4 Sasha Lee null
5 Chris Welsh null

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
left_join_query = f"""
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUST_ID
"""
cursorObj.execute(left_join_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()        

(1, 'Ramesh', None, None)
(2, 'Khilan', 1560, '2009-11-20 00:00:00')
(3, 'Kaushik', 1500, '2009-10-08 00:00:00')
(3, 'Kaushik', 3000, '2009-10-08 00:00:00')
(4, 'Chaital', 2060, '2008-05-20 00:00:00')
(5, 'Hardik', None, None)
(6, 'Komal', None, None)
(7, 'Muffy', None, None)