Inner Join

January 27, 2023

About Inner Join in DBMS

In this article, we will learn about the inner join in DBMS.
Inner Join between two tables helps to retrieve data that will be common to both the tables and thus also referred to as equi
join or simple join.

Inner Join in SQL

Joins enable data merging from multiple tables
Inner join is also called an equi join or simple join.

Functional Dependencies

Joins in DBMS

Relational Algebra

Deadlock in DBMS

Definition of Inner Join

Based on the equality condition data is retrieved from multiple tables
We must have a common column in both tables with the same data type.

Syntax

select col1,col2 
from
table1,tabel2
where table1.comcolname=table2.comcolname;

Inner join Example

Consider two tables :

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-FEBc-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

InnerJoin SQL query

select ename,sal,dept.deptno,dname,dname,loc 
from emp,dept 
where emp.deptno=dept.deptno;

O/P

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

For each and every row in emp table, there is a match with any of the deptno in dept table hence the 14 rows are displayed

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