SQL LEFT JOIN Explained with Practical Examples

This tutorial will explain the use of SQL LEFT JOIN with examples and how one can use it to query data from multiple tables.

 

Introduction to SQL LEFT JOIN

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.

Advertisement

 

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.

SQL LEFT JOIN Explained with Practical Examples

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 Table
  • Table2 represents the Right table
  • join_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.

Advertisement

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
SELECT 
columns_name(s)
FROM Table1
LEFT JOIN Table2
ON 
join_predicate;
SELECT 
Students.StName,Courses.CourseName
FROM Students
LEFT JOIN Courses 
ON
Students.ID = Courses.SID;

As per the above-mentioned syntax of the LEFT JOIN, In this example

Advertisement
  • Students is the Left Table (Table-1)
  • Courses is the Right Table (Table-2)
  • Students.ID = Courses.SID is the join_predicate consisting of (=) equal comparison operator on Students.ID and Courses.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.

Advertisement

 

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.

Advertisement
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 name Raina in the last row.
  • Raina does not have any order in the Orders Table, hence NULL is added in the ordderstatus 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 :

Advertisement
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.

Advertisement

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

SQL JOINS

Categories SQL

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment

X