Khóa học miễn phí MySQL – Left Join nhận dự án làm có lương

MySQL – Left Join

Table of content


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.

Left Join

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 −

ID NAME AGE ADDRESS SALARY
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

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 −

OID DATE CUSTOMER_ID AMOUNT
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

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 −

ID NAME AMOUNT DATE
1 Ramesh NULL NULL
2 Khilan 1560.00 2009-11-20 00:00:00
3 Kaushik 1500.00 2009-10-08 00:00:00
3 Kaushik 3000.00 2009-10-08 00:00:00
4 Chaitali 2060.00 2008-05-20 00:00:00
5 Hardik NULL NULL
6 Komal NULL NULL
7 Muffy NULL NULL

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 −

EID EMPLOYEE_NAME SALES_MADE
102 SARIKA 4500
100 ALEKHYA 3623
101 REVATHI 1291
103 VIVEK 3426

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 −

ID NAME DATE EMPLOYEE_NAME
1 Ramesh NULL NULL
2 Khilan 2009-11-20 00:00:00 REVATHI
3 Kaushik 2009-10-08 00:00:00 ALEKHYA
3 Kaushik 2009-10-08 00:00:00 SARIKA
4 Chaitali 2008-05-20 00:00:00 VIVEK
5 Hardik NULL NULL
6 Komal NULL NULL
7 Muffy NULL NULL

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 − −

ID NAME DATE AMOUNT
3 Kaushik 2009-10-08 00:00:00 3000.00
4 Chaitali 2008-05-20 00:00:00 2060.00

Left Join Using a Client Program

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

Syntax

To join two tables using left join through a PHP program, we need to execute the SQL query with LEFT JOIN clause using the mysqli function query() as follows −

$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);

To join two tables using left join through a JavaScript program, we need to execute the SQL query with LEFT JOIN clause using the query() function of mysql2 library as follows −

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);  

To join two tables using left join through a Java program, we need to execute the SQL query with LEFT JOIN clause using the JDBC function executeQuery() as follows −

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);

To join two tables using left join through a python program, we need to execute the SQL query with LEFT JOIN clause using the execute() function of the MySQL Connector/Python as follows −

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();

Output

The output obtained is as follows −

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);
  });
});

Output

The output produced is as follows −

[
   { 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);
      }
   }
}       

Output

The output obtained is as shown below −

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()        

Output

Following is the output of the above code −

(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)

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc