Sql Left Join

Sql Left Join

Blog Bustle 09/04/2020

Sql left join is used to fetch all data of left table(table 1) and that right table(table 2) data where at least one column value is matched with left table column value and these columns define relationship between both tables and after defining relationship if value does not match, right table data for that left column value would be null. It is one of the important sql joins.

Sql left join syntax

SELECT table_1.*,table_2.* FROM table_1
LEFT JOIN table_2
ON table_1.common_column = table_2.common_column;

Sql left join examples

Let's look at some examples to understand sql left join.

I have two tables named student and student_fees.

id name email
1 justin justin@gmail.com
2 michael michael@gmail.com
3 kevin kevin@gmail.com
4 robert robert@gmail.com
id student_id class_name admission_fee
1 1 6 600
2 2 7 700
3 3 6 600
4 5 8 800

Find student's name,email and class. Use student_fees table as table 1 and student as table 2 and sql query will be

SELECT class_name,name,email
FROM student_fees
LEFT JOIN student
ON student.id = student_fees.student_id;

We can also write this query using sql alias.

SELECT sf.class_name,s.name,s.email
FROM student_fees as sf
LEFT JOIN student as s
ON s.id = sf.student_id;

After running this sql query, result will be

class_name name email
6 justin justin@gmail.com
7 michael michael@gmail.com
6 kevin kevin@gmail.com
8 NULL NULL

Find class and email of students whose admission fee is greater than $600 and sql query will be

SELECT s.email,sf.class_name
FROM student_fees as sf
LEFT JOIN student as s
ON s.id = sf.student_id
WHERE sf.admission_fee > 600;

After running this sql query, result will be

email class_name
michael@gmail.com NULL
7 8

After looking at these examples, we can easily understand and use sql left join. Hope you like this tutorial.

Other important links

Sql Inner Join

Sql Aliases

Sql Group By clause

Sql Limit clause

Sql Order By clause

Sql Left Join searches:

Tags: #mysql