.NET/C#
  • ASP.NET Core Overview
  • ASP.NET Core API tutorial
    • How to create ASP.NET Core C# API tutorial
      • launchSettings
      • Install swagger
        • Swagger best practices
      • Run the app
      • Fix CORS problem
      • Add AutoMapper
      • Add JWT reference
      • Add .gitignore
      • Basic structure & EF Core
      • AddSingleton
    • Loading Related Entities
  • Unit test controller logic in ASP.NET Core
    • Unit testing controllers
  • .NET Q&A
    • dotnet try
    • LINQ
      • LINQ Query Syntax
      • Lambda Expression
      • Standard Query Operators
  • .NET C# Interview questions
    • C# - .NET interview questions and answers [Part 1]
    • C# - .NET interview questions and answers [Part 2]
    • C# Interview questions [Part 3] General questions
  • C#
    • Object-Oriented Programming (Principles)
      • N-tier architecture style
      • Command and Query Responsibility Segregation (CQRS) pattern
      • Project architecture
    • C# Advanced review
      • Implicit typing
      • Generics
      • Attributes
      • Reflection
      • Delegates
      • Anonymous Methods and Lambda Expressions
      • Events
      • Ref vs Out
      • Task
        • TaskFactory Class
  • MySQL
    • MySQL Lerning
      • SELECT
      • ORDER BY
      • WHERE
      • DISTINCT
      • IN
      • BETWEEN
      • Join
      • INNER JOIN
      • LEFT JOIN
      • RIGHT JOIN
      • GROUP BY
      • Subquery
      • UNION
    • Stored Procedures
      • CREATE PROCEDURE
  • Versioning API, MongoDB and ci-cd
    • Create a web API with ASP.NET Core and MongoDB
    • REST API versioning with ASP.NET Core
    • Design a CI/CD pipeline using Azure DevOps
      • Create a CI/CD pipeline for GitHub repo using Azure DevOps Starter
  • TFS & TFCV
    • What is Team Foundation Version Control
      • Develop and share your code in TFVC using Visual Studio
      • Suspend your work and manage your shelvesets
    • Newtonsoft Json.NET
      • Serializing and Deserializing JSON
  • MS-SQL
    • Quick tutorial
      • Add new column to a table
      • LEFT/RIGHT Reverse operator
      • Dates (Transact-SQL)
      • CAST and CONVERT (Transact-SQL)
      • Types of JOIN
      • Our first Left Outer Join
      • CROSS JOIN
Powered by GitBook
On this page
  • MySQL LEFT JOIN examples
  • Condition in WHERE clause vs. ON clause

Was this helpful?

  1. MySQL
  2. MySQL Lerning

LEFT JOIN

PreviousINNER JOINNextRIGHT JOIN

Last updated 4 years ago

Was this helpful?

The LEFT JOIN allows you to query data from two or more tables. Similar to the clause, the LEFT JOIN is an optional clause of the statement, which appears immediately after the FROM clause.

Suppose that you want to join two tables t1 and t2.

The following statement shows how to use the LEFT JOIN clause to join the two tables:

SELECT 
    select_list
FROM
    t1
LEFT JOIN t2 ON 
    join_condition;

When you use the LEFT JOIN clause, the concepts of the left table and the right table are introduced.

In the above syntax, t1 is the left table and t2 is the right table.

The LEFT JOIN clause selects data starting from the left table (t1). It matches each row from the left table (t1) with every row from the right table(t2) based on the join_condition.

If the rows from both tables cause the join condition evaluates to TRUE, the LEFT JOIN combine columns of rows from both tables to a new row and includes this new row in the result rows.

In case the row from the left table (t1) does not match with any row from the right table(t2), the LEFT JOIN still combines columns of rows from both tables into a new row and include the new row in the result rows. However, it uses NULL for all the columns of the row from the right table.

In other words, LEFT JOIN returns all rows from the left table regardless of whether a row from the left table has a matching row from the right table or not. If there is no match, the columns of the row from the right table will contain NULL.

The following Venn diagram helps you visualize how the LEFT JOIN clause works:MySQL LEFT JOIN – Venn Diagram

MySQL LEFT JOIN - Venn Diagram

MySQL LEFT JOIN examples

Let’s take some examples of using the LEFT JOIN clause.

1) Using MySQL LEFT JOIN clause to join two tables

Each customer can have zero or more orders while each order must belong to one customer.

This query uses the LEFT JOIN clause to find all customers and their orders:

SELECT 
    customers.customerNumber, 
    customerName, 
    orderNumber, 
    status
FROM
    customers
LEFT JOIN orders ON 
    orders.customerNumber = customers.customerNumber;
SELECT
    c.customerNumber,
    customerName,
    orderNumber,
    status
FROM
    customers c
LEFT JOIN orders o 
    ON c.customerNumber = o.customerNumber;

In this example:

  • The customers is the left table and orders is the right table.

  • The LEFT JOIN clause returns all customers including the customers who have no order. If a customer has no order, the values in the column orderNumber and status are NULL.

Because both table customers and orders have the same column name ( customerNumber) in the join condition with the equal operator, you can use the USING syntax as follows:

SELECT
	customerNumber,
	customerName,
	orderNumber,
	status
FROM
	customers
LEFT JOIN orders USING (customerNumber);

2) Using MySQL LEFT JOIN clause to find unmatched rows

The LEFT JOIN clause is very useful when you want to find rows in a table that doesn’t have a matching row from another table.

The following example uses the LEFT JOIN to find customers who have no order:

SELECT 
    c.customerNumber, 
    c.customerName, 
    o.orderNumber, 
    o.status
FROM
    customers c
LEFT JOIN orders o 
    ON c.customerNumber = o.customerNumber
WHERE
    orderNumber IS NULL;

3) Using MySQL LEFT JOIN to join three tables

See the following three tables employees, customers, and payments:

This example uses two LEFT JOIN clauses to join the three tables: employees, customers, and payments.

SELECT 
    lastName, 
    firstName, 
    customerName, 
    checkNumber, 
    amount
FROM
    employees
LEFT JOIN customers ON 
    employeeNumber = salesRepEmployeeNumber
LEFT JOIN payments ON 
    payments.customerNumber = customers.customerNumber
ORDER BY 
    customerName, 
    checkNumber;

This picture shows the partial output:

How it works.

  • The first LEFT JOIN returns all employees and customers who represented each employee or NULL if the employee does not in charge of any customer.

  • The second LEFT JOIN returns payments of each customer represented by an employee or NULL if the customer has no payment.

Condition in WHERE clause vs. ON clause

See the following example.

SELECT 
    o.orderNumber, 
    customerNumber, 
    productCode
FROM
    orders o
LEFT JOIN orderDetails 
    USING (orderNumber)
WHERE
    orderNumber = 10123;

This example used the LEFT JOIN clause to query data from the tables orders and orderDetails. The query returns the order and its line items of the order number 10123.

SELECT 
    o.orderNumber, 
    customerNumber, 
    productCode
FROM
    orders o
LEFT JOIN orderDetails d 
    ON o.orderNumber = d.orderNumber AND 
       o.orderNumber = 10123;

It will have a different meaning.

In this case, the query returns all orders but only the order 10123 will have line items associated with it as in the following picture:

In this tutorial, you have learned how to use the MySQL LEFT JOIN clause to join data from two or more tables.

See the following tables customers and orders in the .

Alternatively, you can save some typing by using :

MySQL LEFT JOIN example
MySQL LEFT JOIN unmatched rows
MySQL LEFT JOIN - Condition in WHERE clause

However, if you move the condition from the clause to the ON clause:

MySQL LEFT JOIN - Condition in ON clause

Notice that for clause, the condition in the ON clause is equivalent to the condition in the WHERE clause.

sample database
table aliases
WHERE
INNER JOIN
INNER JOIN
SELECT