Inner Join

Inner Join in DBMS

 

Join  is a query that combines rows from two or more tables, views, materialized views i.e When data from more than one table in the database is required a join condition is used

The most common type of join is  inner join mechanism also called as equal join or simple join.

 

Learn more about Join in DBMS here on this page.

Inner Join in DBMS

Inner join Definition

  • Tables are joined based on equality condition
  • Only matching records are displayed
  • Joining tables must have at least one common same data type and the same values.

 

Syntax

select col1,col2,col3.... 
from table1,table2
where table1.common col name = table2.common col name;

 

Example for Inner Join

Consider two tables emp and dept with the following data

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-FEB-81125050030
7654MARTINSALESMAN769828-SEP-811250140030
7844TURNERSALESMAN769808-SEP-811500030
7876ADAMSCLERK778823-MAY-87110020
7900JAMESCLERK769803-DEC-8195030
7934MILLERCLERK778223-JAN-82130010

 

Dept table

DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON

 

Write SQL Query to display data from two tables emp and dept wherever there is common dept no and from the sales department

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

O/P

ROWNUMEMPNOENAMEDEPTNODNAMEANNSALLOC
17698BLAKE30SALES34200CHICAGO
27499ALLEN30SALES19200CHICAGO
37521WARD30SALES15000CHICAGO
47654MARTIN30SALES15000CHICAGO
57844TURNER30SALES18000CHICAGO
67900JAMES30SALES11400CHICAGO

 

We are having 6 rows in emp table from the sales department, with dept no=30 and matches with deptno=30 which is appended with the data in dept table as a result total of 6 rows are displayed