Left Join in SQL

February 1, 2023

About left join in SQL

In this article, we will learn about left join in SQL.
Left Join in SQL is used to retrieve data from tables that is common top both the tables along with the complete data of the first table i.e. the left table.

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.

Functional Dependencies

Joins in DBMS

Relational Algebra

Deadlock in DBMS

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

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	–	17-NOV-81	5000	–	10
7698	BLAKE	MANAGER	7839	01-MAY-81	2850	–	30
7782	CLARK	MANAGER	7839	09-JUN-81	2450	–	10
7566	JONES	MANAGER	7839	02-APR-81	2975	–	20
7788	SCOTT	ANALYST	7566	19-APR-87	3000	–	20
7902	FORD	ANALYST	7566	03-DEC-81	3000	–	20
7369	SMITH	CLERK	7902	17-DEC-80	800	–	20
7499	ALLEN	SALESMAN	7698	20-FEB-81	1600	300	30
7521	WARD	SALESMAN	7698	22-FEB-81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-SEP-81	1250	1400	30
7844	TURNER	SALESMAN	7698	08-SEP-81	1500	0	30
7876	ADAMS	CLERK	7788	23-MAY-87	1100	–	20
7900	JAMES	CLERK	7698	03-DEC-81	950	–	30
7934	MILLER	CLERK	7782	23-JAN-82	1300	–	10

Dept table

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

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

ROWNUM	ENAME	SAL	DEDTNO	DNAME	LOC
1	KING	5000	10	ACCOUNTING	NEW YORK
2	CLARK	2450	10	ACCOUNTING	NEW YORK
3	MILLER	1300	10	ACCOUNTING	NEW YORK
4	JONES	2975	20	RESEARCH	DALLAS
5	SCOTT	3000	20	RESEARCH	DALLAS
6	FORD	3000	20	RESEARCH	DALLAS
7	SMITH	800	20	RESEARCH	DALLAS
8	ADAMS	1100	20	RESEARCH	DALLAS
9	BLAKE	2850	30	SALES	CHICAGO
10	ALLEN	1600	30	SALES	CHICAGO
11	WARD	1250	30	SALES	CHICAGO
12	MARTIN	1250	30	SALES	CHICAGO
13	TURNER	1500	30	SALES	CHICAGO
14	JAMES	950	30	SALES	CHICAGO

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Left Join in SQL

About left join in SQL

Left Join in SQL

How Left join works

Syntax

Left Join Example

Left join SQL query

Prime Course Trailer

Related Banners

Get over 200+ course One Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription