The SQL LEFT JOIN is a type of join that returns all rows from the left table and only matched rows from the right table. It returns zero rows even if it does not find any matched row. In case no matched row is found in the right table, NULL is being added in front of all rows of left tables. Hence the maximum cardinality from the left table is all the records and on the other hand from the right table, the minimum cardinality is zero records.
Venn Representation of LEFT JOIN
Set theory and Venn diagrams are being used to describe the relations(tables) and other relational database concepts like queries, predicates, etc. Resultant rows of LEFT JOIN can also be represented by the following Venn diagram for two tables. It is also called LEFT OUTER JOIN in a few database books and references.
LEFT JOIN - VENN DIAGRAM REPRESENTATION
SQL LEFT JOIN Syntax
The basic syntax of SQL LEFT JOIN is as under:
SELECT columns_name(s)
FROM Table1
LEFT JOIN Table2
ON
join_predicate;
Here,
Table1
means Left TableTable2
represents the Right tablejoin_predicate
represent the conditional expression with ON clause
This conditional expression will be a comparison expression made up with comparison and logical operators of SQL. It is to remind you that Comparison Operators are >
, <
, >=
, <=
, =
, !=
, LIKE
, BETWEEN
, etc and Logical Operators are AND
, OR
, and NOT
.
SQL LEFT JOIN Examples
Consider that we have the following two tables of Students and Course.
Students Table consists of five attributes with ID as Primary Key.
ID | StName | Age | City | Fee |
---|---|---|---|---|
1 | Stephen | 23 | London | 2000 |
2 | Lucas | 32 | New York | 2100 |
3 | Brendon | 34 | Oslo | 2300 |
4 | Ahmad | 31 | Mumbai | 3400 |
5 | Joe | 45 | China | 2300 |
Courses Table also consists of five attributes with CID(courseID) as Primary key and SID(StudentID) as Foreign key.
CID | CourseName | Duration | SID | CR |
---|---|---|---|---|
101 | Computing | 3 months | 1 | 3 |
201 | Programming | 4 months | 2 | 3 |
301 | Data Structures | 6 Months | 1 | 3 |
401 | OOP | 2 Months | 3 | 2 |
Students and Courses Tables have the one-to-many relationship between them. One to Many relationships states that one student can register for zero or more courses. When SQL LEFT JOIN will be applied on these tables ON Students.ID
and Courses.SID
, all records of students tables should be in the output table with matched records in Courses Table.
The Students who have not registered for any course yet must also be in the output with NULL
from the right table.
Example-1: List Student Name and Course Name
Suppose we have a Students table and Courses table in our database. Suppose you want to see the list of students and the courses taken by them. You can use LEFT JOIN on these two tables as under:
SELECT Students.StName, Courses.CourseName
FROM Students
LEFT JOIN Courses
ON
Students.ID = Courses.SID
Comparison of SQL LEFT JOIN Syntax and Code of Example 1
Syntax | Example 1 CODE |
|
|
As per the above-mentioned syntax of the LEFT JOIN, In this example
- Students is the Left Table (Table-1)
- Courses is the Right Table (Table-2)
Students.ID = Courses.SID
is thejoin_predicate
consisting of (=) equal comparison operator onStudents.ID
andCourses.SID
ID | STNAME | COURSENAME |
---|---|---|
1 | Stephen | Computing |
1 | Stephen | Data Structure |
2 | Lucas | Programming |
3 | Brendon | OOP |
4 | Ahmad | NULL |
5 | Joe | NULL |
Observing the output, you can see that
- All rows of the Students table ID (Left Table) and the matched rows of Courses Table SID (Right Table)are in the output.
- Since Stephen's CID is mapped to two different courses in Courses SID, so you can see that Stephen has two registered courses.
- Ahmad and Joe have no registered courses as their Student ID is not mapped to any of the Course SID, hence
NULL
is being added against these rows in the column Course Name in the output. - The order of the output table will be the same as the order of rows in the Left Table.
- We will discuss the ORDER BY clause at the end of this article too.
Example 2: List Student Name with Fee and Duration
Let us join these two tables again for some other columns using the LEFT JOIN.
SELECT Students.ID, Students.StName, Students.Fee, Course.Duration
FROM Students
LEFT JOIN Courses
ON Students.ID = Courses.SID;
This would produce the following result:
ID | STNAME | FEE | DURATION |
---|---|---|---|
1 | Stephen | 2000 | 3 Months |
1 | Stephen | 2000 | 6 Months |
2 | Lucas | 2100 | 4 Months |
3 | Brendon | 2300 | 2 Months |
4 | Ahmad | 3400 | NULL |
5 | Joe | 2300 | NULL |
Observing the output, You can see that
- All records of the Students Table (Left Table) are present in the output.
- Row with student name Stephen is repeated twice because it has two matches in Courses Table based on the ID (Right Table)
- Row with student name Ahmed and Joe do not have any match in Courses table, hence
NULL
is added in the duration columns against these. - Order in the output Table is the order of the records in the LEFT Table until we use the
ORDER BY
clause in LEFT JOIN.
Use of WHERE Clause in SQL LEFT JOIN
- WHERE CLAUSE can also be used with LEFT JOIN in conjunction with ON Clause.
- As you know, WHERE CLAUSE is used to filter the rows in a table or multiple tables.
- In LEFT JOIN, it performs the same filtering function before applying LEFT JOIN.
- Use of WHERE CLAUSE in LEFT JOIN filters the record of either LEFT Table or RIGHT Table first and then performs the join with ON Clause.
Syntax of WHERE Clause in SQL left JOIN
Here is the syntax of how you can use WHERE CLAUSE with SQL LEFT JOIN.
SELECT columns_name(s)
FROM Table1
LEFT JOIN Table2
ON
Join_predicate
WHERE CLAUSEpredicate;
SQL WHERE CLAUSE
is used to create matches between two tables under some specific circumstances.
Example-1: List Customer Name, Address and Order Status
Here is another example to explain the use of WHERE CLAUSE with LEFT JOIN.
- Suppose we have two tables Customers and Orders.
- You need to list the address, name, and orderstatus of the suspended customers.
ID | CName | address | accountstatus |
---|---|---|---|
1 | Max | Ahmedabad | blocked |
2 | Brown | Oslo | Active |
3 | Suresh | Mumbai | Active |
4 | Raina | Hyderabad | Suspended |
ID | CustomerID | orderstatus | CartID | OrderDate |
---|---|---|---|---|
1 | 1 | booked | 1 | 12/12/21 |
2 | 3 | pending | 3 | 10/10/21 |
3 | 2 | delivered | 2 | 11/11/21 |
4 | 3 | delivered | 4 | 5/5/21 |
Suppose we want to join the Customers table and Orders Table for only those customers whose accountstatus
is not suspended. Here is the required SELECT statement with LEFT JOIN and WHERE CLAUSE :
SELECT Customers.CName, Customers.address, Orders.orderstatus, Customers.accountstatus
FROM Customers LEFT JOIN Orders
ON Customers.ID = Orders.CustomerID
WHERE Customers.accountstatus <>"suspended";
<>
is used as Not Equal to Operator (!=
)
The OUTPUT table will be as under:
CName | address | orderstatus | accountstatus |
---|---|---|---|
Max | Ahmedabad | booked | blocked |
Brown | Oslo | pending | Active |
Suresh | Mumbai | delivered | Active |
Suresh | Mumbai | pending | Active |
Observe the output now, WHERE Clause was applied on "Customers.accountstatus
". In Customers Table shown above it can be seen that there exist three customers who do not have 'suspended
' status.
- WHERE Clause has fileted all these three records. We have three records in the output table from the LEFT table.
- From Right Table, all these Three records filtered by WHERE clause got a match with Order Status.
- Hence all the Records from LEFT Table have been joined with three Matched values from RIGHT Table.
- There is no NULL in this LEFT Join because all the Filtered records have one-to-one to match in the Right Table.
- Order in the output table is observed as per the order of the LEFT Table (by default) until we will apply for the ORDER BY Clause with LEFT JOIN.
Example-2: List Customer Name, Address, Order, and Account Status (without WHERE clause)
Let us do the LEFT JOIN of these two tables without the WHERE Clause to understand the difference. Here is the LEFT JOIN query for Customers and Orders table on CustomerID between these.
SELECT Customers.CName, Customers.address, Orders.orderstatus, Customers.accountstatus
FROM Customers
LEFT JOIN Orders
ON Customers.ID = Orders.CustomerID;
OUTPUT
CName | address | orderstatus | accountstatus |
---|---|---|---|
Max | Ahmedabad | booked | blocked |
Brown | Oslo | pending | Active |
Suresh | Mumbai | delivered | Active |
Suresh | Mumbai | pending | Active |
Raina | Hyderabad | Null | Suspended |
It is apparent from the output table that we will have a different output in SQL LEFT Join without the use of the WHERE Clause. Here in the output, it can be observed that:
- There are a total of six records, Row with the Customer name
Suresh
is present two times as it has two matching Orders in the Orders Table. - All records of the LEFT table are in the output with matched values from the Right table in
ordderstatus
Column except for the customer nameRaina
in the last row. - Raina does not have any order in the Orders Table, hence
NULL
is added in theordderstatus
Column as per the primitive functionality of LEFT JOIN.
Use of Logical Operators in LEFT JOIN (ON Clause / WHERE CLAUSE)
You can also use logical operators (AND
, OR
, NOT
, ALL
, ANY
, LIKE
, IN
, etc.) in the ON clause and in WHERE CLAUSE as well.
In the above example, we have used WHERE Clause and have used !=(not equal to) Operator in LEFT JOIN. If you want to use more than one Join_Predicates or WHERE_Predicates, you need to use logical operators. For example, you want to apply two conditions in ON Clause
or Two Conditions in the WHERE Clause.
Let's take another example on the same tables Customers and Orders.
Suppose we want to see the list of those Customers whose accoutstatus
is either suspended
or blocked
. Hence you will need to apply two predicates/conditions here in this SQL LEFT JOIN query. We will use two WHERE Clauses in the Select statement as follows :
SELECT Customers.CName, Customers.address, Orders.orderstatus, Customers.accountstatus
FROM Customers LEFT JOIN Orders
ON Customers.ID = Orders.CustomerID
WHERE (((Customers.accountstatus)="suspended"))
OR (((Customers.accountstatus)="blocked"));
OUTPUT
CName | address | orderstatus | accountstatus |
---|---|---|---|
Max | Ahmedabad | booked | blocked |
Raina | Hyderabad | delivered | Suspended |
Now you will have only two records in your Customers Table with accoutnstatus
either suspended
or blocked
. We have used the OR logical operator in the WHERE CLAUSE and the output is done. Similarly, logical operators can be applied with ON Clause as well.
Use of ORDER BY Clause with LEFT JOIN
It has been mentioned in every example and in every output, the order of the output rows is taken from the order of the LEFT Table. You can use the ORDER BY clause if you want to change the order of the output table. If we want to see the resultant records of LEFT JOIN in a specific order using any Specific Column-name, you can use the ORDER BY Clause in the following way.
Let consider again the example from the Students and Courses Table. Suppose we want to see the list of students and courses with Credit Hours. But we want to see the output in descending order of the credit hours of a course. The required SELECT statement with LEFT OUTER JOIN will be as under :
SELECT Students.StName, Students.Age, Courses.CourseName, Courses.CR
FROM Students LEFT JOIN Courses
ON Students.ID = Courses.SID
ORDER BY Courses.CR DESC;
OUTPUT
StName | Age | CourseName | CR |
---|---|---|---|
Lucas | 32 | Programming | 3 |
Stephen | 23 | Data Structures | 3 |
Stephen | 23 | Computing | 3 |
Brendon | 34 | OOP | 2 |
Joe | 45 | NULL | NULL |
Ahmad | 31 | NULL | NULL |
This time output is the same as the output of Example 1, but the order of the rows is different. It is due to the use of the ORDER BY clause with LEFT JOIN. The number of records is again the same as that of output in example 1. NULL addition is also the same the difference is the ORDER. As far as ORDER is concerned, it is not important in INNER JOINS but it is important in SQL LEFT OUTER JOINS. LEFT JOIN is neither commutative and not associative.
It means that the output of a LEFT JOIN b
is not the same as b LEFT JOIN a
. LEFT JOIN and all other OUTER joins are not associative either. Let us check this property by applying the queries on Students and Courses Tables.
Example-1: Students LEFT JOIN Courses
SELECT Courses.CID, Courses.CourseName, Courses.Duration, Students.City, Courses.SID
FROM Students LEFT JOIN Courses
ON Students.ID = Courses.SID;
OUTPUT
CID | CourseName | Duration | City | SID |
---|---|---|---|---|
101 | Computing | 3 Months | London | 1 |
301 | Data Structures | 6 Months | London | 1 |
201 | Programming | 4 Months | New York | 2 |
401 | OOP | 2 Months | Oslo | 3 |
Mumabi | ||||
China |
Blanks means NULL
Example-2: Courses LEFT JOIN Students
SELECT Courses.CID, Courses.CourseName, Courses.Duration, Courses.SID, Students.City
FROM Courses LEFT JOIN Students
ON Courses.SID = Students.ID;
OUTPUT
CID | CourseName | Duration | SID | City |
---|---|---|---|---|
101 | Computing | 3 Months | 1 | London |
201 | Programming | 4 Months | 2 | New York |
301 | Data Structures | 6 Months | 1 | London |
401 | OOP | 2 Months | 3 | Oslo |
It is apparent from these two outputs that SQL LEFT JOIN is not associative. There is a significant difference between the number of rows and the orders of rows. In Students, LEFT JOINS Courses, LEFT Table is Students and all records of Students are checked for matched records in Courses which is Right Table. In Courses LEFT Join Students, Courses becomes the Left Table and all of its records are being matched with the records of Students table.
Summary
The SQL LEFT JOIN clause allows you to query data from multiple tables. It returns all rows from the left table and the matching rows from the right table. In short, the LEFT JOIN clause returns all rows from the left table (Table1) and matching rows or NULL values from the right table (Table2). Like all other SELECT queries, you can use the WHERE Clause, ORDER BY clause. Multiple conditions can be logically concatenated in WHERE Clauses and in the ON clause as well.
Further Readings