Joins in DBMS

January 31, 2023

About Joins in DBMS

In this article, we will learn about joins in DBMS.
Joins in Database Management System are used to retrieve data from multiple tables. Data can be extracted based on multiple conditions which maybe applied while retrieving the information.

Joins in DBMS

Joints are used to retrieve data from multiple tables i.e it is used for data merging

there are five different types of joints provided by ANSI, let’s discuss each of them in brief, Also called as 9I joins.

There are multiple types of joins in DBMS :

1. Inner Join
2. Right Join
3. Left Join
4. Full Join
5. Cross Join
6. Natural Join

Functional Dependencies

Joins in DBMS

Relational Algebra

Deadlock in DBMS

Inner Join

Definition: The inner join returns only matching rows, here columns that are used in join condition must belong to the same type, when tables having common column then only we are allowed how to use inner join
Inner join performance is very high compared to Oracle 8i equal join

Inner Join Example

select ename,loc from
emp
join 
dept
on emp.deptno=dept.deptno
where loc='CHICAGO';

consider two sample tables emp and dept

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

O/P(inner join)

ROWNUM	ENAME	LOC
1	BLAKE	CHICAGO
2	ALLEN	CHICAGO
3	WARD	CHICAGO
4	MARTIN	CHICAGO
5	TURNER	CHICAGO
6	JAMES	CHICAGO

Left outer join

Definition: Left join always returns all rows from the left table and matching rows from right side table and also returns null values in the place of nonmatching rows in another table

Left join Example

select rownum ,ename,loc from
emp
LEFT join 
dept
on emp.deptno=dept.deptno 
where loc='DALLAS';

O/P

ROWNUM	ENAME	LOC
1	JONES	DALLAS
2	SCOTT	DALLAS
3	FORD	DALLAS
4	SMITH	DALLAS
5	ADAMS	DALLAS

Right outer join

Definition: Right join always return all rows from right side table and matching rows from the left table and also returns null values in the place of nonmatching rows in another table

Example for Right Outer join

select rownum,ename,loc from
emp
RIGHT join 
dept
on emp.deptno=dept.deptno where loc!='CHICAGO' ;

O/P

ROWNUM	ENAME	LOC
1	KING	NEW YORK
2	CLARK	NEW YORK
3	JONES	DALLAS
4	SCOTT	DALLAS
5	FORD	DALLAS
6	SMITH	DALLAS
7	ADAMS	DALLAS
8	MILLER	NEW YORK
9	–	BOSTON

Full Outer join

Definition: Full join returns all rows from both tables because it is viewed as a union of a left, right and inner join, It also returns null values in the place of nonmatching rows tuples

Full Outer join example

select rownum,ename,loc,job,mgr from
emp
FULL OUTER JOIN 
dept
on emp.deptno=dept.deptno ;

O/P

ROWNUM	ENAME	LOC	JOB	MGR
1	KING	NEW YORK	PRESIDENT	–
2	BLAKE	CHICAGO	MANAGER	7839
3	CLARK	NEW YORK	MANAGER	7839
4	JONES	DALLAS	MANAGER	7839
5	SCOTT	DALLAS	ANALYST	7566
6	FORD	DALLAS	ANALYST	7566
7	SMITH	DALLAS	CLERK	7902
8	ALLEN	CHICAGO	SALESMAN	7698
9	WARD	CHICAGO	SALESMAN	7698
10	MARTIN	CHICAGO	SALESMAN	7698
11	TURNER	CHICAGO	SALESMAN	7698
12	ADAMS	DALLAS	CLERK	7788
13	JAMES	CHICAGO	CLERK	7698
14	MILLER	NEW YORK	CLERK	7782
15	–	BOSTON	–	–

Natural join

Natural Join also returns matching rows only, this join performance is very high compared to inner join Definition: In this join, you are not required to use join condition explicitly, in this case, resource tables must have a common column, based on this oracle server itself internal establishes join condition

Natural Join Example

select rownum,ename,loc from
emp
NATURAL join 
dept
where loc='CHICAGO';

O/P

ROWNUM	ENAME	LOC
1	BLAKE	CHICAGO
2	ALLEN	CHICAGO
3	WARD	CHICAGO
4	MARTIN	CHICAGO
5	TURNER	CHICAGO
6	JAMES	CHICAGO

Cross join

Definition: In this type of join data in each and, every row in one table is added to all the rows in another table Here cross product operation performed

Here we have 14 rows in emp table and 4 rows in dept table hence 14*4=56 records are displayed if the cross join is performed on these tables without condition specified

Cross Join example

select rownum,ename,sal,dname,loc from 
emp 
cross join
dept
where loc='CHICAGO';

O/P

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

Heera 14 records are displayed because there is one row in dept table with location Chicago and that row is appended to all the rows in emp table

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

Checkout list of all the video courses in PrepInsta Prime Subscription

3 comments on “Joins in DBMS”

Shubham
sorry i understand it my mistake….

0

Log in to Reply
Shubham
sorry i understand my mistake….

2

Log in to Reply
Shubham
In inner join there should be same name attribute in both tables, but in above emp table there is no loc attribute whereas in dept table it is present then how the query will be execute succesfully?

1

Log in to Reply