What is left join

For example, you can join employees and departments tables together to get the department name for each employee. For example, you could use a left join to get a list of the department name for each employee. Get matched to a bootcamp today. The average bootcamp grad spent less than six months in career transition, from starting a bootcamp to finding their first job. We use the ON statement to determine the condition on which our tables are connected.

Thus, you may see this type of join referred to as left outer joins. Say that we want to get a list of the department names where each employee works. We want to retrieve this information using one query. Here is a query that would allow us to get this data:. On the first line of our query, we specify that we want to get three columns.

We retrieve the name of our employees, their titles, and the name of the department for which they work. On the next line, we specify that we want to get information from our employees table. If we have an employee with a department ID of 9 and that department does not exist, they will still appear in our query.

If we have a company department that does not exist, it will not appear in our JOIN query. These are the values for our new employee:. They also return rows from the right table where the JOIN condition is met. 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. See the following tables customers and orders in the sample database. Alternatively, you can save some typing by using table aliases :.

First of all, the database looks into each row of the left table and searches for a match in the right table based on the related columns. If there is a match, it adds data from the right table to the corresponding row of the left table.

If there are several matches like in our case with customer 3 , it duplicates the row in the left table to include all records from the right table. If there is no match, it still keeps the row from the left table and puts NULL in the corresponding columns of the right table customer 2 in our example. We want to join these tables so we can see who received bonuses in January. Our result should include all employees, no matter if they received a bonus or not. As expected, the result includes all employees.

If an employee is not found in the table with bonus info, the corresponding columns from the second table are filled in with NULL values. We have a list of countries with some basic information and we want to supplement it with the GDP data for , where such is available.