Left Join in SQL

Left Join in SQL

 

In this article, we will learn about the Left Join in SQL.

Join enables data merging from tables i.e  used to query data from multiple tables

How Left join works

  • Left Join always Returns all rows from the left table
  • Matching records from the right side table
  • Null values in the place of non-matching rows in another table.

 

Learn more about the Joins in SQL here on this page.

Left Join in SQL

Syntax

select col1,col2.... 
from 
tab1 
LEFT JOIN 
tab2 
on tab1.colname=tab2.colname

 

Left Join Example

Consider two tables emp and dept table :

Emp table

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080020
7499ALLENSALESMAN769820-FEB-81160030030
7521WARDSALESMAN769822-FEB-81125050030
7654MARTINSALESMAN769828-SEP-811250140030
7844TURNERSALESMAN769808-SEP-811500030
7876ADAMSCLERK778823-MAY-87110020
7900JAMESCLERK769803-DEC-8195030
7934MILLERCLERK778223-JAN-82130010

 

Dept table

DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON

 

Left join SQL query

select rownum,ename,sal,d.deptno,dname,loc
from 
emp e 
left outer join
dept d
on e.deptno=d.deptno;

O/P

ROWNUMENAMESALDEPTNODNAMELOC
1KING500010ACCOUNTINGNEW YORK
2CLARK245010ACCOUNTINGNEW YORK
3MILLER130010ACCOUNTINGNEW YORK
4JONES297520RESEARCHDALLAS
5SCOTT300020RESEARCHDALLAS
6FORD300020RESEARCHDALLAS
7SMITH80020RESEARCHDALLAS
8ADAMS110020RESEARCHDALLAS
9BLAKE285030SALESCHICAGO
10ALLEN160030SALESCHICAGO
11WARD125030SALESCHICAGO
12MARTIN125030SALESCHICAGO
13TURNER150030SALESCHICAGO
14JAMES95030SALESCHICAGO