Inner Join

Inner Join in SQL

 

In this article, we will learn about Inner Join in SQL.

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

 

Learn more about Joins here on this page.

Inner Join in SQL

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

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080020
7499ALLENSALESMAN769820-FEB-81160030030
7521WARDSALESMAN769822-FEBc-81125050030
7654MARTINSALESMAN769828-SEP-811250140030
7844TURNERSALESMAN769808-SEP-811500030
7876ADAMSCLERK778823-MAY-87110020
7900JAMESCLERK769803-DEC-8195030
7934MILLERCLERK778223-JAN-82130010

Dept table

DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON

InnerJoin SQL query

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

O/P

ROWNUMENAMESALDEPTNODNAMEDNAMELOC
1KING500010ACCOUNTINGACCOUNTINGNEW YORK
2BLAKE285030SALESSALESCHICAGO
3CLARK245010ACCOUNTINGACCOUNTINGNEW YORK
4JONES297520RESEARCHRESEARCHDALLAS
5SCOTT300020RESEARCHRESEARCHDALLAS
6FORD300020RESEARCHRESEARCHDALLAS
7SMITH80020RESEARCHRESEARCHDALLAS
8ALLEN160030SALESSALESCHICAGO
9WARD125030SALESSALESCHICAGO
10MARTIN125030SALESSALESCHICAGO
11TURNER150030SALESSALESCHICAGO
12ADAMS110020RESEARCHRESEARCHDALLAS
13JAMES95030SALESSALESCHICAGO
14MILLER130010ACCOUNTINGACCOUNTINGNEW 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