MySQL Left Join

How to write MySQL Left Join with an example using Command Prompt and Workbench?. MySQL Left Join or Left outer join is to return all the records (or rows) from the Left table, and matching rows from the right table.

MySQL Left Join Syntax

The basic syntax of Left Join in MySQL is as shown below:

-- SQL Server LEFT JOIN Syntax
SELECT Table1.Column(s), Table2.Column(s)
FROM Table1
 LEFT JOIN
     Table2 ON
   Table1.Common_Column = Table2.Common_Column

--OR We can Simply Write it as
SELECT Table1. Column(s), Table2. Column(s)
FROM Table1
 LEFT OUTER JOIN Table2 
      ON Table1.Common_Column = Table2.Common_Column

Let us see the visual representation of the MySQL Left Outer join for better understanding.

TIP: Left Outer join can also call as Left Join. So it is optional for you to use the Outer Keyword.

From the above image, you can understand easily that the MySQL Left Outer join displays all the records present in Table A, and matching records from Table B.

NOTE: All the Unmatched rows from right table will fill with NULL Values.

To demonstrate the Left Outer Join, we are going to use Employ, and Department tables present in our company Database. The data present in employ table is:

Data present in the Department Table is:

MySQL Left Join Examples

The following are the list of ways we can use this MySQL Left Outer Join to combine two tables or get information (records) from two or more tables.

MySQL Left Join Select * Example

The following left outer join example query will display all the columns present in the employ table and matching records from the Department table.

-- MySQL Left Outer Join Example
USE company;
SELECT *  FROM employ
    LEFT JOIN department
	ON employ.DeptID = department.DeptID;

If you observe the below screenshot, Left Outer Join is displaying all the 15 records from the Employ table. But for [DeptID], Department Name, and Standard_Salary, it is displaying NULL Values for the EmpID number 4, 5, 9, 11, and 15.

It is because DeptID for those records in the Employ table is NULL. So there are no matching records in the right table.

NOTE: The DeptID column repeated twice, which might be annoying to the end-user. To avoid unwanted columns, I suggest you select individual column names. Please avoid SELECT * Statement in Left Join

MySQL Left Join select Few Columns Example

As we said before, please use the required columns after the SELECT Statement to avoid unwanted columns

-- MySQL Left Outer Join Example
USE company;
SELECT First_Name, Last_Name, Education, 
       Yearly_Income, Sales,
       DepartmentName, Standard_Salary
FROM employ
    LEFT JOIN department
	ON employ.DeptID = department.DeptID;

OUTPUT

Ambiguous Columns in MySQL Left Join Example

The above-specified query will work entirely as long as the column names from both employ and Department are different like above. What happens if they had the same column names in both the tables? Well, with the above-specified approach, you will end up in a mess. Let us see how to resolve the issue.

Let me show you one practical example. As you can see, we are using the same left join query. But, we added DepID from the department table as an additional column.

-- MySQL Left Outer Join Example
USE company;
SELECT  First_Name, Last_Name, Education, 
        Yearly_Income, Sales,
	DeptID, DepartmentName, Standard_Salary
FROM employ
	LEFT JOIN department
		ON employ.DeptID = department.DeptID;

As you can see, it is throwing an error: Ambiguous column DeptID. It is because, DeptID column is present in both tables, and MySQL query doesn’t know which column you are asking it to display.

To resolve this kind of issue, always use the table name before the column name. The following query is using the ALIAS table name before the column names.

By this approach, we can inform the query that we are looking for DepID belonging to the department table.

We can simply write the above query as:

-- MySQL Left Outer Join Example
USE company;
SELECT  emp.First_Name,	emp.Last_Name, emp.Education,
	emp.Yearly_Income, emp.Sales,
        dept.DeptID, dept.DepartmentName, dept.Standard_Salary
FROM employ AS emp
	LEFT JOIN department AS dept
		ON emp.DeptID = dept.DeptID;

OUTPUT

NOTE: It is always a best practice to use the table name before the Column name in MySQL Left Outer Join. For example, SELECT employ.First_Name

MySQL Left Join using Keyword

If the common column name in both the tables is the same, then you can use the USING keyword. Since DeptID is the common column name in both the tables, above MySQL left outer join query can also return as

-- MySQL Left Outer Join Example
USE company;
SELECT  emp.First_Name,	emp.Last_Name, emp.Education,
	emp.Yearly_Income, emp.Sales,
        dept.DeptID, dept.DepartmentName, dept.Standard_Salary
FROM employ AS emp
	LEFT JOIN department AS dept
		USING(DeptID);

OUTPUT

MySQL Left Join Multiple Conditions

Until now, we are showing the = operator as the condition to the left outer join employ and department table. But you can replace = with < (less than), > (greater than), or not equal to operators.

To join those two tables, this example, we are using two conditions in the left outer join.

The following query will display all the records from the employ table and matching records from the department table. Here matching records means, DeptID of both tables should match, and Standard Salary should be greater than 1000000

-- MySQL Left Outer Join Example
USE company;
SELECT  empl.First_Name, empl.Last_Name, empl.Education, 
        empl.Yearly_Income, empl.Sales,
	dept.DepartmentName, dept.Standard_Salary
FROM employ AS empl
    LEFT JOIN department AS dept
	ON empl.DeptID = dept.DeptID AND
           dept.Standard_Salary > 1000000;

OUTPUT

I think you are confused about the result. Let me replace the greater than symbol with less than. It means, DeptID of both tables should match, and Standard Salary should be less than 1000000

MySQL Left Join Where Clause Example

In this example, we will show you how to use Where clause (to apply filter) along with the Left outer Join. I suggest you refer MySQL Where Clause article.

-- MySQL Left Outer Join Example
USE company;
SELECT  empl.First_Name, empl.Last_Name, empl.Education, 
        empl.Yearly_Income, empl.Sales,
	dept.DepartmentName, dept.Standard_Salary
FROM employ AS empl
    LEFT JOIN department AS dept
	ON empl.DeptID = dept.DeptID
WHERE Sales > 20000;

OUTPUT

MySQL Left Outer Join Command prompt Example

Let me show you, How to write Left Outer Join in Command prompt.

In this example, we will also show how to use Order By clause along with the Left Outer Join. I suggest you refer to MySQL Order By article.

-- MySQL Left Outer Join Example
USE company;
SELECT  empl.First_Name, empl.Last_Name, empl.Education, 
	depat.DepartmentName, depat.Standard_Salary,
        empl.Yearly_Income, empl.Sales
FROM employ AS empl
    LEFT JOIN department AS depat
 	ON empl.DeptID = depat.DeptID
ORDER BY First_Name, Last_Name;

OUTPUT