Inner Join

July 7, 2019

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>

Example for Inner Join

Consider two tables emp and dept with the following data

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

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

ROWNUM	EMPNO	ENAME	DEPTNO	DNAME	ANNSAL	LOC
1	7698	BLAKE	30	SALES	34200	CHICAGO
2	7499	ALLEN	30	SALES	19200	CHICAGO
3	7521	WARD	30	SALES	15000	CHICAGO
4	7654	MARTIN	30	SALES	15000	CHICAGO
5	7844	TURNER	30	SALES	18000	CHICAGO
6	7900	JAMES	30	SALES	11400	CHICAGO

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