











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>


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
Login/Signup to comment