MySQL Left Join is one of the Join Type which is used to return all the records (or rows) from Left table, and matching rows from the right table. In this article we will show you, How to write MySQL Left Join with example using Command Prompt, and Workbench.
NOTE: All the Unmatched rows from right table will be filled with NULL Values.
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
TIP: In MySQL, Left Outer join can also be called as Left Join. So it is an optional for you to use the Outer Keyword.
Let us see the visual representation of the MySQL Left Outer join for better understanding.
From the above image you can understand easily that, Left Outer join displays all the records present in Table A, and matching records from Table B. To demonstrate the MySQL Left Outer Join, we are going to use Employ, and Department tables present in our company Database.
Data present in employ table is:
Data present in the Department Table is:
MySQL Left Join Examples
Following are the list of ways that we can use this MySQL Left Outer Join to combine two tables, or get information (records) from two or more tables.
MySQL Left Join – Using Select *
The following MySQL left outer join Query will display all the columns present in employ table, and matching records from 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 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 . This is because, DeptID for those records in Employ table are NULLS so there is no matching records in right table.
NOTE: The DeptID column was repeated twice, which might be annoying to end-user. In order to avoid unwanted columns, I suggest you to select individual column names. Please avoid SELECT * Statement in Left Join
MySQL Left Join – Select Few Columns
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
Above specified query will work perfectly as long as the column names from both employ, and Department are different like above. What happens if they had 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 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. This is because, DeptID column is present in both tables, and MySQL query don’t know which column you are asking it to display.
To resolve these kind of issues you always have to use table name before the column name. The following query is using ALIAS table name before the column names. By this approach, we can inform the query that we are looking for DepID belonging to 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 Left Join. For example, SELECT employ.First_Name
MySQL Left Join – Using Keyword
If the common column name in both the tables are same then you can use USING keyword. Since DeptID is the common column name in both the tables, above query can also be returned 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 – Using Multiple Conditions
Until now, we are showing the = operator as the condition to left join employ and department table. But you can replace = with < (less than), > (greater than), or not equal to operators. In this example we are using two condition to left join those two tables.
Following query will display all the records from employ table, and matching records from 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 with 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 Example – Where Clause
In this example we will show you, How to use Where clause (to apply filter) along with the Left outer Join. I suggest you to 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 Join Example – Command prompt
Let me show you, How to write Left Outer Join query using the MySQL Command prompt. In this example we will also show you, How to use Order By clause along with the Left Outer Join. I suggest you to refer 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
Thank You for Visiting Our Blog
