Prev Next
- SQL LEFT JOIN is used to combine the two tables together. LEFT JOIN selects all records from left table and also selects all matching records from the right table.
- And, LEFT JOIN selects all records from left table, even though there are no matching records in the right table. In this scenario, all selected right column values will be returned as NULL.
- LEFT JOIN is also called as a LEFT OUTER JOIN.
SQL Syntax for Left Join (Left outer join):
| SQL Syntax for LEFT JOIN (LEFT OUTER JOIN) | SELECT table1.column1, table2.column2, etc FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name; |
| Or Another Equivalent SQL Syntax for LEFT JOIN (LEFT OUTER JOIN) |
SELECT table1.column1, table2.column2, etc FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name; |
Please consider following 2 tables with few records as given below.
Table1:
Table name (for example): student1
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows
Table2:
Table name (for example): student2
Column names in this table: Student_ID, Department, College and Rank
Available records: 4 rows
Example: how to use SQL Left Join (Left outer join):
SQL Query:
SELECT student1. Student_name, student1.City, student2. Department, student2.Rank
FROM student1
LEFT JOIN student2
ON student1.Student_ID = student2.Student_ID;
Or
SELECT student1. Student_name, student1.City, student2. Department, student2.Rank
FROM student1
LEFT OUTER JOIN student2
ON student1.Student_ID = student2.Student_ID;
Description:
In above SQL LEFT JOIN, all 4 records are returned from left table ‘student1’. And, all matching records are selected from the right table. And, there is one extra matching record for ‘Student_ID’ = 2 is also returned from the right table. So, totally 5 records are returned and displayed in the output. Also, please note that for the record ‘Student_ID’ = 3 in the left table, there is no matching record in the right table. So, NULL values are returned and displayed in the output.
SQL query Output:
CONTINUE ON OTHER SQL JOINS:
Click on each SQL joins below for detailed description and example SQL queries.
| Type of SQL JOINS | Description |
| SQL – Inner Join (Simple Join) | It is used to combine two or more tables together using the matching columns from both tables. |
| SQL – Left Join (Left outer join) | LEFT JOIN selects all records from left table and also selects all matching records from the right table. |
| SQL – Right Join (Right outer join) | RIGHT JOIN selects all records from right table and also selects all matching records from the left table. |
| SQL – Full Join (Full outer join) | FULL JOIN selects and returns all records from both left and right tables. |
| SQL – Self Join | Self Join is used to join a table to itself. |
| SQL – Cartesian Join or Cross Join | Cartesian Join returns the Cartesian products of 2 or more tables joined together. |