Left Join in DBMS

Left Join in DBMS

 

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

A join is a query that combines rows from two or more tables, views or materialized views.

Definition: A left join is an outer join where it returns all the records from the left table and matching records from the right table.

 

Learn more about Joins in DBMS here on this page.

Left Join in DBMS

Three points  about left outer join

  1. Tables are join based on some condition.
  2. Only matched records from right side table and all records from the left side table  are displayed.
  3. The result is Null if there is no match from the right side table.

 

Syntax:

select col1,col2,col3.... 
from <table 1>
LEFT JOIN 
<table 2>
on <table 1>.common_column = <table 2>.common_column;

 

Left Join Example

Consider two tables  emp and dept tables

Emp table

EMPNOENAMEJOB  MGR
HIREDATE  SAL
COMMDEPTNO
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,Empno,Ename,Emp.Deptno,Dname, sal*12 annsal,loc,job
from 
Emp
LEFT JOIN
Dept
on Emp.Deptno=Dept.deptno and dname='SALES';

O/P

ROWNUMEMPNOENAMEDEPTNODNAMEANNSALLOCJOB
17698BLAKE30SALES34200CHICAGOMANAGER
27499ALLEN30SALES19200CHICAGOSALESMAN
37521WARD30SALES15000CHICAGOSALESMAN
47654MARTIN30SALES15000CHICAGOSALESMAN
57844TURNER30SALES18000CHICAGOSALESMAN
67900JAMES30SALES11400CHICAGOCLERK
77839KING1060000PRESIDENT
87782CLARK1029400MANAGER
97934MILLER1015600CLERK
107566JONES2035700MANAGER
117788SCOTT2036000ANALYST
127902FORD2036000ANALYST
137369SMITH209600CLERK
147876ADAMS2013200CLERK

 

There are 6 matched records with deptno=30 and dname =’SALES’ from dept table with emp table and 9 records in emp table that are unmatched in that case if there is no data to be appended  from right side table null values are placed hence total 14 rows are displayed.