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

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 <table>,<table2>
where <table 1>.common col name>=<table 2.common col name>
Inner Join in DBMS

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

Please Login/Signup to comment