Left Join in SQL

The command of LEFT JOIN would return all rows present in the left table along with the matching rows present in the right table. And, if there is no match, the result from the right side is NULL.

In this article, we will take a look at the Left Join in SQL according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

Table of Contents

What is Left Join in SQL?
- - Syntax
- Example
Practice Questions on Left Join in SQL
FAQs

What is Left Join in SQL?

The Left Join in SQL would return all the rows from the left table, even when no match exists in the right table. It means that in case the ON clause would match zero (0) records from the right table, then the Left Join would still return some row in the final results. The only thing is that there will be NULL in every column present in the right table.

Now it means that the returns from the Left Join would be all the values present in the left table along with the values matched from the right table. Now, the results would be NULL in case no matching join predicate exists.

Syntax

Here is the basic syntax of the LEFT JOIN:

SELECT table_a.column_a, table_b.column_b…

FROM table_a

LEFT JOIN table_b

ON table_a.common_field = table_b.common_field;

The given condition here could be a given expression on the basis of your requirement.

Now let us take a look at an example.

Example

We will now consider the two tables given as follows:

Table 1 − CLIENTS Table is given as follows:

ID	NAMETAG	ADDRESS	AGE	WAGE
1	Rimona	Ahmedabad	32	2000.00
2	Kujo	Delhi	25	1500.00
3	Kiara	Kota	23	2000.00
4	Chilly	Mumbai	25	6500.00
5	Helen	Bhopal	27	8500.00
6	Kitty	MP	22	4500.00
7	Mindy	Indore	24	10000.00

Table 2 − Orders Table is given as follows:

OID	DATE	CLIENT_ID	TOTAL
102	2019-10-18 00:00:00	3	3000
100	2019-10-18 00:00:00	3	1500
101	2019-11-10 00:00:00	2	1560
103	2018-05-10 00:00:00	4	2060

Now, we will join the given two tables as follows using the LEFT JOIN:

SQL> SELECT ID, NAMETAG, AMOUNT, DATE

FROM CLIENTS

LEFT JOIN ORDERS

ON CLIENTS.ID = ORDERS.CLIENT_ID;

As a result, it would produce the result as follows:

ID	NAMETAG	TOTAL	DATE
1	Rimona	NULL	NULL
2	Kujo	1560	2019-11-10 00:00:00
3	Kiara	3000	2019-10-18 00:00:00
3	Kiara	1500	2019-10-18 00:00:00
4	Chilly	2060	2018-05-10 00:00:00
5	Helen	NULL	NULL
6	Kitty	NULL	NULL
7	Mindy	NULL	NULL

Practice Questions on Left Join in SQL

1. The left outer join preserves the tuples only in a relation that is named before:

A. Left outer join operation

B. Left inner join operation

C. Right inner join operation

D. Right outer join operation

Answer: A. Left outer join operation

2. Which of these is NOT a type of join in SQL?

A. Left Join

B. Right Join

C. Inner Join

D. Back Join

Answer: D. Back Join

FAQs

What is a left join in SQL?

The command of LEFT JOIN would return all rows present in the left table along with the matching rows present in the right table. And, if there is no match, the result from the right side is NULL.

What are the 3 types of joins in SQL?

Here are the 3 different types of joins in SQL:
1. (INNER) JOIN: It returns the records that consist of matching values in both the available tables.
2. LEFT (OUTER) JOIN: It returns all the records present in the left table along with the matched records present in the right table.
3. RIGHT (OUTER) JOIN: It returns all the records present in the right table along with the matched records present in the left table.

What is the difference between left and right outer join?

The primary difference between the Left and Right Join exists in the inclusion of the non-matched rows. On the one hand, the left join would include the unmatched rows from the table that is present on the join clause’s left. The Right outer join, on the other hand, would do the same on the join clause’s right.

Is the join statement the same as the LEFT join statement?

The JOIN statement is similar to that of the LEFT JOIN statement. Here, the primary difference is basically that the LEFT JOIN statement would include all the rows of an entity or a table that is referenced on the statement’s left side.

Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus, GATE Previous Year Question Paper, and more.

Also Explore,