Open In App

SQL LEFT JOIN

Last Updated : 21 Jun, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

SQL LEFT JOIN command returns all records from the left table and matching records from the right table. If there is no matching record in the right table, the right table records will contain NULL values.

LEFT JOIN in SQL

LEFT JOIN in SQL is used to combine rows from two or more tables, based on a related column between them. It returns all rows from the left table and matching records from the right table.

If a certain row is present in the left table but not in the right table, the result will include this row, but with a NULL value in each column from the right table. If a record from the right table is not on the left, it will not be included in the result.

SQL LEFT JOIN Venn Diagram

This VENN diagram shows how a LEFT JOIN works in SQL.

Left Join

LEFT JOIN

Syntax

The LEFT JOIN syntax is:

SELECT column_name(s) 
FROM tableA 
LEFT JOIN tableB ON tableA.column_name = tableB.column_name;

SQL LEFT JOIN Example 

Let’s look at an example of LEFT JOIN in SQL to understand it better.

Let’s consider two tables Emp containing details of the Employee working in the particular department, and department table containing the details of the department

Employee Table

Query:

CREATE TABLE Emp (
EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Country VARCHAR(50),
Age INT,
Salary INT,
department_id INT
);

INSERT INTO Emp (EmpID, Name, Country, Age, Salary, department_id)
VALUES (1, 'Shubham', 'India', 23, 30000, 101),
(2, 'Aman', 'Australia', 21, 45000, 102),
(3, 'Naveen', 'Sri Lanka', 24, 40000, 103),
(4, 'Aditya', 'Austria', 21, 35000, 104),
(5, 'Nishant', 'Spain', 22, 25000, 101);

Output:

employee table

Employee Table

Department Table 

Query:

  CREATE TABLE department (
department_id INT PRIMARY KEY,
department_name VARCHAR(50),
department_head VARCHAR(50),
location VARCHAR(50)
);

INSERT INTO department (department_id, department_name, department_head, location)
VALUES (101, 'Sales', 'Sarah', 'New York'),
(102, 'Marketing', 'Jay', 'London'),
(103, 'Finance', 'Lavish', 'San Francisco'),
(104, 'Engineering', 'Kabir', 'Bangalore');
SELECT * FROM department;

Output:

department table

Department Table

To perform left-join on  these two tables we will use the following SQL query:

SELECT Emp.EmpID, Emp.Name, department.
department_name, department.department_head,
department.location
FROM Emp
LEFT JOIN department ON Emp.department_id = department.department_id;

Output:

left join in sql example output

LEFT JOIN in SQL Example Output

As left join gives the matching rows and the rows that are present in the left table but not in the right table. Here in this example, we see that the employees that do not work in a particular department, i.e., having dept no values as [NULL], contain [NULL] values of dept name and location after the left join.

SQL LEFT JOIN with WHERE Clause Example

In this example, we will add a WHERE clause that specifies to only return results where the “location” column in the department table equals ‘Bangalore’. This will filter the results to only show employees who belong to a department located in Bangalore, and departments that have no employees will not be returned in the results. 

Query: 

SELECT e.EmpID, e.Name, d.department_name, 
d.department_head, d.location
FROM Emp e
LEFT JOIN department d ON e.department_id
= d.department_id
WHERE d.location = 'Bangalore';

Output: 

sql left join with where clause example output

SQL LEFT JOIN with WHERE Clause Example

SQL LEFT JOIN as Aliases Example

In this query, we’ll use aliases “e” for the Emp table and “d” for the department table. The SELECT statement references these aliases for each column, making the query easier to read and type. Aliases simplify code and improve readability, especially with long or complex table names.

Query:

SELECT e.EmpID, e.Name, d.department_name, 
d.department_head, d.location
FROM Emp e
LEFT JOIN department d ON
e.department_id = d.department_id;

Output:

sql left join as aliases example output

SQL LEFT JOIN as Aliases Example Output

 Important Points About SQL LEFT JOIN

  • LEFT JOIN in SQL returns all records from the left table and matching records from the right table.
  • NULL values are included for unmatched records on the right side.
  • LEFT JOIN is used to combine data based on related columns.
  • Aliases can simplify queries with long table names.
  • LEFT JOIN with WHERE clause is used for filtering records.


Previous Article
Next Article

Similar Reads

SQL Left Outer Join vs Left Join
In SQL, both LEFT JOIN and LEFT OUTER JOIN are used to combine data from two or more tables based on a related column, but they are essentially the same operation, and there is no practical difference between them. The keyword OUTER is optional in most database systems, including popular ones like MySQL, PostgreSQL, and SQL Server. What is a Left J
5 min read
SQL | Join (Cartesian Join & Self Join)
SQL| JOIN(Inner, Left, Right and Full Joins) In this article, we will discuss about the remaining two JOINS: CARTESIAN JOIN SELF JOIN Consider the two tables below: StudentCourse CARTESIAN JOIN: The CARTESIAN JOIN is also known as CROSS JOIN. In a CARTESIAN JOIN there is a join for each row of one table to every row of another table. This usually h
2 min read
Difference Between Left Join and Left Outer Join
In SQL language, different joins are used to assemble rows from two or more tables from the related column. The terms "Left Join" and "Left Outer Join" are used interchangeably in SQL but they refer to the same concept. A Left Join retrieves all records from the left table (the first table in the query) and matches them with records from the right
5 min read
SQL Full Outer Join Using Left and Right Outer Join and Union Clause
An SQL join statement is used to combine rows or information from two or more than two tables on the basis of a common attribute or field. There are basically four types of JOINS in SQL. In this article, we will discuss FULL OUTER JOIN using LEFT OUTER Join, RIGHT OUTER JOIN, and UNION clause. Consider the two tables below: Sample Input Table 1: Pu
3 min read
Left join and Right join in MS SQL Server
Prerequisite – Introduction of MS SQL Server 1. Left Join : A join combines the set of two tables only. A left join is used when a user wants to extract the left table's data only. Left join not only combines the left table's rows but also the rows that match alongside the right table. Syntax - select select_list from table1 left join table2 on joi
2 min read
Difference Between Left Join and Right Join
In DBMS(Database Management System) Join is an operation that combines the row of two or more tables based on related columns between them. The main purpose of Join is to retrieve the data from multiple tables in other words Join is used to perform multi-table queries. So for that purpose, joins come into the picture. So in this article, we will go
5 min read
Difference between Natural join and Inner Join in SQL
The join operation merges the two tables based on the same attribute name and their datatypes are known as Natural join Unlike INNER JOIN, which requires you to specify the columns and conditions for the join explicitly. In this article, we will also see the differences between them. Let's start with Natural Join. Example: If you have two tables "S
3 min read
Difference between Natural join and Cross join in SQL
1. Natural Join : Natural Join joins two tables based on same attribute name and datatypes. The resulting table will contain all the attributes of both the tables but only one copy of each common column. Example: Consider the two tables given below: Student Table Marks Table Consider the given query SELECT * FROM Student S NATURAL JOIN Marks M; Out
2 min read
Self Join and Cross Join in MS SQL Server
Prerequisite - Introduction of MS SQL Server 1. Self Join : Self-join allows us to join a table itself. It is useful when a user wants to compare the data (rows) within the same table. Syntax - select select_list from T t1 [Inner|Left] Join on T t2 on join_predicate. Here T refers to the table we use for comparison and it is referred twice. To avoi
2 min read
SQL | EQUI Join and NON EQUI JOIN
Types of SQL Joins are explained in left, right, and full join and SQL | Join (Cartesian Join & Self Join). And Remaining EQUI Join and NON-EQUI will discuss in this article. Let's discuss one by one. SQL JOINS : EQUI JoinNON-EQUI Join Example - Let's Consider the two tables given below. Table name — Student In this table, you have I'd, name, c
2 min read
Implicit Join vs Explicit Join in SQL
JOIN clause is used to combine rows from two or more tables, based on a relation between them. There are two different syntax forms to perform JOIN operation: Explicit joinImplicit join Step 1: Creating the Database Use the below SQL statement to create a database called geeks: CREATE DATABASE geeks; Step 2: Using the Database Use the below SQL sta
3 min read
Full join and Inner join in MS SQL Server
Full Join Full join selects all the rows from the left and right tables along with the matching rows as well. If there are no matching rows, it will be displayed as NULL. Syntax: select select_list from table1 full join table2 on join _predicate (OR) select table1.*, table2.* from table1 full join table2 on join _predicate (OR) select * from table1
2 min read
Difference between Inner Join and Outer Join in SQL
JOINS in SQL are fundamental operations used to combine data from multiple tables based on related columns. They are essential for querying data that is distributed across different tables, allowing you to retrieve and present it as a single or similar result set. In this article, We will learn about Inner Join vs Outer Join in SQL in detail. What
5 min read
How to Left Join Multiple Tables in SQL
Left Join is one of the Keywords used while writing queries in SQL. In SQL we normally use Join for the purpose of forming a new table by taking out common data like rows or records or tuples from both the tables which are having matching records in general. Here when it comes to Left Join in SQL it only returns all the records or tuples or rows fr
3 min read
SQL Joins (Inner, Left, Right and Full Join)
SQL Join operation combines data or rows from two or more tables based on a common field between them. In this article, we will learn about Joins in SQL, covering JOIN types, syntax, and examples. SQL JOINSQL JOIN clause is used to query and access data from multiple tables by establishing logical relationships between them. It can access data from
5 min read
Difference between Nested Loop join and Sort Merge Join
1. Nested Loop Join : Nested Loop Join is the simplest join algorithm which usually has better performance than all other types of joins due to a lesser number of comparisons involved in it. Each row in the outer table is compared to each row in the inner table. The Nested Loop Join algorithm for 2 relations R and S : For each record x of R read in
3 min read
Difference between Hash Join and Sort Merge Join
1. Hash Join : It is also known as "go-to-guy" in case of join operators. This means that in case no other join is preferred (maybe due to no sorting or indexing etc), then, Hash join is used. Hash join is best algorithm when large, unsorted, and non-indexed data (residing in tables) is to be joined. Hash join algorithm consists of probe phase and
3 min read
Join Multiple Tables Using Inner Join
To retrieve data from the single table we use SELECT and PROJECTION operations but to retrieve data from multiple tables we use JOINS in SQL. There are different types of JOINS in SQL. In this article, only operations on inner joins in MSSQL are discussed. Inner Join is the method of retrieval of data from multiple tables based on a required condit
3 min read
Difference Between “INNER JOIN” and “OUTER JOIN”
Joins in SQL are essential tools that allow us to combine rows from multiple tables based on specific conditions, often involving a relation between columns in those tables. These joins help in pulling related data together in a meaningful way. Among the most commonly used joins are INNER JOIN and OUTER JOIN. In this article, We will learn about th
5 min read
Difference Between Anti-Join and Semi-Join
In the context of SQL, Anti-join, and semi-join are two essential operations in relational databases used for querying and manipulating data. These operations focus on comparing data from two related tables, but they serve distinct purposes. In this article let us discuss these two operations in detail along with some examples. What is SQL Join?Joi
5 min read
Inner Join vs Outer Join
Inner Join and Outer Join are the types of join. The inner join has the work to return the common rows between the two tables, whereas the Outer Join has the work of returning the work of the inner join in addition to the rows that are not matched.  Let's discuss both of them in detail in this article. Before moving ahead, let's discuss what is Joi
9 min read
Difference Between Right Join and Right Outer Join
Joins in a Database (SQL) are mostly used for combining data or the rows of two or more table records that are based on the same or common attribute. There are various types of Joins like Right Join, Left Join, Full Join, etc. Each join has its own syntax and data-returning capability. In this article, we will see the information about Right Join a
5 min read
Difference Between Nested Loop Join and Hash Join
These join operations are important to the optimization of SQL operations, especially in guaranteed cases concerning database management systems. Mostly where clause conditions can be transformed into Nested Loop Join and Hash Join main two methods of joining two or more data tables on the same attribute. Nested Loop Join is a simple method, usuall
6 min read
Difference between Left, Right and Full Outer Join
When working with SQL and relational databases, joins are essential tools that allow you to combine data from multiple tables based on related columns. Among the various types of joins, Left Join, Right Join, and Full Outer Join are commonly used for different scenarios. Understanding the differences between these joins is crucial for efficient dat
6 min read
Difference between JOIN and UNION in SQL
Pre-requisites: JOIN, UNION JOIN in SQL is used to combine data from many tables based on a matched condition between them. The data combined using the JOIN statement results in new columns. Consider the two tables: Boys Girls Example: sql> SELECT Boys.Name, Boys.Age, Girls.Address, FROM Boys INNER JOIN Girls ON Boys.Rollno = Girls.Rollno; The r
2 min read
SQL | UPDATE with JOIN
SQL UPDATE JOIN could be used to update one table using another table and join condition. Syntax - UPDATE tablename INNER JOIN tablename ON tablename.columnname = tablename.columnname SET tablenmae.columnnmae = tablenmae.columnname; Use multiple tables in SQL UPDATE with JOIN statement. Let us assume we have two tables - Geeks1 and Geeks2. To check
2 min read
SQL Outer Join
In a relational DBMS, we follow the principles of normalization that allows us to minimize the large tables into small tables. By using a select statement in Joins, we can retrieve the big table back. Outer joins are of following three types. Left outer joinRight outer joinFull outer join Creating a database : Run the following command to create a
3 min read
SQL Natural Join
Natural join is an SQL join operation that creates a join on the base of the common columns in the tables. To perform natural join there must be one common attribute(Column) between two tables. Natural join will retrieve from multiple relations. It works in three steps. In this article, we will discuss the overview of SQL Natural Join and then main
3 min read
SQL Full Outer Join Using Union Clause
In this article, we will discuss the overview of SQL, and our main focus will be on how to perform Full Outer Join Using Union Clause in SQL. Let's discuss it one by one. Overview :To manage a relational database, SQL is a Structured Query Language to perform operations like creating, maintaining database tables, retrieving information from the dat
3 min read
SQL Full Outer Join Using Where Clause
A SQL join statement is used to combine rows or information from two or more than two tables on the basis of a common attribute or field. There are basically four types of JOINS in SQL. In this article, we will discuss about FULL OUTER JOIN using WHERE clause. Consider the two tables below: Sample Input Table 1 : PURCHASE INFORMATIONProduct_IDMobil
3 min read
Article Tags :
three90RightbarBannerImg