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

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

Joins in DBMS
DBMS desktop gif
DBMS mobile adv gif

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 Prime
DBMS Tutorial banner

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

Get Prime
Course List
Course List

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout
Checkout

3 comments on “Joins in DBMS”


  • 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?