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.

IDStNameAgeCityFee
1Stephen23London2000
2Lucas32New York2100
3Brendon34Oslo2300
4Ahmad31 Mumbai3400
5Joe45China2300

 

Courses Table also consists of five attributes with CID(courseID) as Primary key and SID(StudentID) as Foreign key.

Table-2 (Courses)
CIDCourseNameDurationSIDCR
101Computing3 months13
201Programming4 months23
301Data Structures6 Months13
401OOP2 Months32

 

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

  • 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

 

Example Output-1
IDSTNAMECOURSENAME
1StephenComputing
1StephenData Structure
2LucasProgramming
3BrendonOOP
4AhmadNULL
5JoeNULL

 

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.

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

Example Output-2
IDSTNAMEFEEDURATION
1Stephen20003 Months
1Stephen20006 Months
2Lucas21004 Months
3Brendon23002 Months
4Ahmad3400NULL
5Joe2300NULL

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.
Customer Table
IDCNameaddressaccountstatus
1MaxAhmedabadblocked
2BrownOsloActive
3SureshMumbaiActive
4RainaHyderabadSuspended
Orders Table
IDCustomerIDorderstatusCartIDOrderDate
11booked112/12/21
23pending310/10/21
32delivered211/11/21
43delivered45/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:

CNameaddressorderstatusaccountstatus
MaxAhmedabadbookedblocked
BrownOslopendingActive
SureshMumbaideliveredActive
SureshMumbaipendingActive

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

CNameaddressorderstatusaccountstatus
MaxAhmedabadbookedblocked
BrownOslopendingActive
SureshMumbaideliveredActive
SureshMumbaipendingActive
RainaHyderabadNullSuspended

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 :

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

CNameaddressorderstatusaccountstatus
MaxAhmedabadbookedblocked
RainaHyderabaddeliveredSuspended

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

StNameAgeCourseNameCR
Lucas32Programming3
Stephen23Data Structures3
Stephen23Computing3
Brendon34OOP2
Joe45NULLNULL
Ahmad31NULLNULL

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

CIDCourseNameDurationCitySID
101Computing3 MonthsLondon1
301Data Structures6 MonthsLondon1
201Programming4 MonthsNew York2
401OOP2 MonthsOslo3
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

CIDCourseNameDurationSIDCity
101Computing3 Months1London
201Programming4 Months2New York
301Data Structures6 Months1London
401OOP2 Months3Oslo

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

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