This is a detailed tutorial of SQL LEFT JOIN. Learn to fetch all records from the LEFT database table that matches with the records from the RIGHT table.
Use of SQL LEFT JOIN
SQL LEFT JOIN will fetch all the records from the LEFT database table for which the records of the RIGHT database table share a common column value for a number of records. If in case, a value of the LEFT database table is not contained in the JOINING column of the RIGHT database table, the records will still be fetched but the not found values will be fetched as NULL records. Again, this may seem a bit complicated to beginners, but after having a look at examples, you’ll be clear of this concept.
Note. LEFT JOIN and LEFT OUTER JOIN are the same. It’s just that in some databases, we have to use the full keyword LEFT OUTER JOIN instead of LEFT JOIN.
LEFT JOIN Syntax
1 2 3 4 | SELECT table_1.col_1, table_1.col_2, table_2.col_1, table_2.col_2 ... FROM table_1 LEFT JOIN table_2 ON table_1.column_x = table_2.column_x; |
In the above syntax of LEFT JOIN, we’re using a SQL SELECT Statement to fetch all of the records from the table_1 and also the matching records values from the table_2 on the basis of the joining column present in both tables i.e. column_x. If the record values for column_x are not present in the table_2, then the record values from the second table still are fetched but as NULL.
Example
For the demonstration query of LEFT JOIN, we’ll use the two demo database tables employees and salaries. These tables have one common column emp_no that will be used for LEFT JOINING both of these tables. The following two screenshots give a quick look at the columns and data of these two tables. Observe them carefully.
employees
salariesThe following query will fetch all the records from the LEFT database table employees and also the matching record values from the RIGHT database table salaries on the basis of JOINING column emp_no.
1 2 | SELECT employees.emp_no, employees.first_name, employees.last_name, salaries.salary FROM employees LEFT JOIN salaries ON employees.emp_no = salaries.emp_no; |
Observe the result set screenshot of the above query carefully.
As you can see in the salaries table, there are no records for emp_no 10001 and 10003, therefore the value of the salary column is fetched as NULL in the resultset of the LEFT JOIN query. Therefore, this example justifies the working of SQL LEFT JOIN query.
I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.
Also, don’t forget to Subscribe to WTMatter!
