SQL LEFT JOIN

SQL LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

LEFT JOIN Syntax:

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name = table2.column_name;


Customer table:

id

name     

age 

  address
salary

1

Ramesh

32

Ahmedabad 
2000.00 

2

Khilan

25

Delhi     
1500.00 

3

Kaushik

23

Kota      
2000.00 
4Chaitali25Mumbai    
6500.00 
5Hardik27Bhopal    
8500.00 
6Komal22MP        
4500.00 
7Muffy24Indore    
10000.00 


 Orders table:

oid

  date 

customer_id

Amount

102 

 2009-10-08

3

3000 

100 

2009-10-08

3

1500 

101 

2009-11-20 

2

1560 
103 
2008-05-20
42060 


Example : 

 SELECT  ID, NAME, AMOUNT, DATE

   FROM CUSTOMERS

   LEFT JOIN ORDERS

   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

The OUTPUT for the above query:

id

name

amount

date

1

Ramesh     

NULL  

NULL

2

Khilan     

1560  

2009-11-20

3

kaushik     

3000  

2009-10-08
3kaushik   
1500  
2009-10-08

Chaitali 
2060  
2008-05-20
5Hardik   
NULL 
NULL 
6Komal    
NULL 
NULL 
7Muffy    
NULL 
NULL