Outer Join in DBMS

January 31, 2023

About Outer Join in DBMS

In this article, we will learn about outer join in DBMS.
Outer Join between two tables helps to retrieve data that will be common to both the tables along with the unmatched data between the tables and thus helps to collect complete data from both the tables.

Full Outer Join in DBMS

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

Definition: Full join returns all the matched records and unmatched records from both left side table and right side table.

A Full join is viewed as a union of an inner join, left outer join and right outer join.

Functional Dependencies

Joins in DBMS

Relational Algebra

Deadlock in DBMS

How full join works

It Returns all the records that are matched based upon the condition (inner join)
It Returns all the unmatched records from the right table (right outer)
It Returns all the unmatched record from the left table (left outer)
It places normal values on unmatched records when there is no data to be appended for some tuples.

Syntax:

select 
col1,col2,col3.... 
from  table1
FULL JOIN table2
on table1.common_col=table2.common_col;

Consider two tables Emp table

EMPNO	ENAME	JOB	MGR	HIRE_DATE	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

Full Join SQL query

select rownum,Empno,Ename,Emp.Deptno,Dname, sal*12 annsal,loc,job
from 
Emp
FULL JOIN
Dept
on Emp.Deptno=Dept.deptno and dname='SALES';

O/P

ROWNUM	EMPNO	ENAME	DEPTNO	DNAME	ANNUAL	LOC	JOB
1	7839	KING	10	–	60000	–	PRESIDENT
2	7698	BLAKE	30	SALES	34200	CHICAGO	MANAGER
3	7782	CLARK	10	–	29400	–	MANAGER
4	7566	JONES	20	–	35700	–	MANAGER
5	7788	SCOTT	20	–	36000	–	ANALYST
6	7902	FORD	20	–	36000	–	ANALYST
7	7369	SMITH	20	–	9600	–	CLERK
8	7499	ALLEN	30	SALES	19200	CHICAGO	SALESMAN
9	7521	WARD	30	SALES	15000	CHICAGO	SALESMAN
10	7654	MARTIN	30	SALES	15000	CHICAGO	SALESMAN
11	7844	TURNER	30	SALES	18000	CHICAGO	SALESMAN
12	7876	ADAMS	20	–	13200	–	CLERK
13	7900	JAMES	30	SALES	11400	CHICAGO	CLERK
14	7934	MILLER	10	–	15600	–	CLERK
15	–	–	–	OPERATIONS	–	BOSTON	–
16	–	–	–	ACCOUNTING	–	NEW YORK	–
17	–	–	–	RESEARCH	–	DALLAS	–

There are 6 matched records with deptno=30 and name =’SALES’ from dept table with emp table and 3 records from right side table ‘dept’ are unmatched are displayed ,if there is no data to be added on unmatched records null values are added and 9 unmatched records from left side table with null values when there is no data to be appended for tuples
6(inner join matched records)+9(unmatched records from the left table)+3(unmatched records from the right table)=17 records (full outer join )

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