SQL LEFT JOIN

sql left join

SQL LEFT JOIN

SQL LEFT JOIN

     

The LEFT JOIN keyword retrieve all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

Understand with Example

The Tutorial illustrate an example from SQL LEFT JOIN. In this example, we describe you, how to use left join in table using select statement.

create table statement in SQL is used to create a tables 'Stu_Table' and 'Lib_Table'.  

Create Table Stu_Table

create table Stu_Table(Stu_Id integer(2), Stu_Name varchar(15), Stu_Class  varchar(10))

Create Table Lib_Table

create table Lib_Table(Stu_Id integer(2), Lib_no integer(5))

Insert data into Stu_Table

The insert into add the records or rows to the respective tables.  

insert into Stu_Table values(1,'Komal',10);
insert into Stu_Table values(2,'Ajay',10);
insert into Stu_Table values(3,'Rakesh',10);
insert into Stu_Table values(4,'Bhanu',10);
insert into Stu_Table values(5,'Santosh',10);
insert into Stu_Table values(6,'Tanuj',10);

 Insert data into Lib_Table

insert into Lib_Table values(1,101)
insert into Lib_Table values(2,102)
insert into Lib_Table values(3,103)
insert into Lib_Table values(4,104)
insert into Lib_Table values(5,105)

Stu_Table

Stu_Id Stu_Name Stu_Class
1 Komal 10
2 Ajay 10
3 Rakesh 10
4 Bhanu 10
5 Santosh 10
6 Tanuj 10

Lib_Table

Stu_Id Lib_no
1 101
2 102
3 103
4 104
5 105

SQL INNER JOIN Syntax

The INNER JOIN  keyword is used to returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).The syntax for SQL INNER JOIN is given as :

SELECT column_name(s)
FROM table_name1
Left JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

Use INNER JOIN in SQL Query

In this example, the inner join is used to return the records from a tables 'Stu_Table'and 'Lib_Table' using select statement. The select statement include the set of column records to be retrieved from both tables. The Left Join is used to return all rows from the left table ,even if there is no matches in the right table.

select s.stu_id, s.stu_name, s.stu_class, l.lib_no 
from stu_table as s Left JOIN lib_table as l
on l.stu_id = s.stu_id

Ruselt

Stu_Id Stu_Name Stu_Class Lib_No
1 Komal 10 101
2 Ajay 10 102
3 Rakesh 10 103
4 Bhanu 10 104
5 Santosh 10 105
6 Tanuj 10 NULL