Joins in DBMS

Joins in DBMS

 

In this article, we will learn about Joins in DBMS.

Joints are used to retrieve data from multiple tables i.e it is used for data merging

there are five different types of joints provided by ANSI, let’s discuss each of them in brief, Also called as 9I joins.

 

Learn more about DBMS here on this page.

Joins in DBMS

Inner Join

  • Definition: The inner join returns only matching rows, here columns that are used in join condition must belong to the same type, when tables having common column then only we are allowed how to use inner join
  • Inner join performance is very high compared to Oracle 8i equal join

Inner Join Example

select ename,loc from
emp
join 
dept
on emp.deptno=dept.deptno
where loc='CHICAGO';
types of Joins in DBMS
  • consider two sample tables emp and dept

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
O/P(inner join)
ROWNUM ENAME LOC
1 BLAKE CHICAGO
2 ALLEN CHICAGO
3 WARD CHICAGO
4 MARTIN CHICAGO
5 TURNER CHICAGO
6 JAMES CHICAGO

Left outer join

Definition: Left  join always returns all rows from the left table and matching rows from right side table and also returns null values in the place of nonmatching rows in another table

Left join Example

select rownum ,ename,loc from
emp
LEFT join 
dept
on emp.deptno=dept.deptno 
where loc='DALLAS';
O/P
ROWNUM   ENAME   LOC
1 JONES DALLAS
2 SCOTT DALLAS
3 FORD DALLAS
4 SMITH DALLAS
5 ADAMS DALLAS

Right outer join

Definition: Right join always return all rows from right side table and matching rows from the left table and also returns null values in the place of nonmatching rows in another table

Example for Right Outer join

select rownum,ename,loc from
emp
RIGHT join 
dept
on emp.deptno=dept.deptno where loc!='CHICAGO' ;
O/P
ROWNUM ENAME LOC
1 KING NEW YORK
2 CLARK NEW YORK
3 JONES DALLAS
4 SCOTT DALLAS
5 FORD DALLAS
6 SMITH DALLAS
7 ADAMS DALLAS
8 MILLER NEW YORK
9 BOSTON

Full Outer join

Definition: Full join returns all rows from both  tables because it is viewed as a union of a left, right and inner join, It also returns null values in the place of nonmatching rows tuples

Full Outer join example

select rownum,ename,loc,job,mgr from
emp
FULL OUTER JOIN 
dept
on emp.deptno=dept.deptno ;
O/P
ROWNUM ENAME LOC JOB   MGR
1 KING NEW YORK PRESIDENT
2 BLAKE CHICAGO MANAGER 7839
3 CLARK NEW YORK MANAGER 7839
4 JONES DALLAS MANAGER 7839
5 SCOTT DALLAS ANALYST 7566
6 FORD DALLAS ANALYST 7566
7 SMITH DALLAS CLERK 7902
8 ALLEN CHICAGO SALESMAN 7698
9 WARD CHICAGO SALESMAN 7698
10 MARTIN CHICAGO SALESMAN 7698
11 TURNER CHICAGO SALESMAN 7698
12 ADAMS DALLAS CLERK 7788
13 JAMES CHICAGO CLERK 7698
14 MILLER NEW YORK CLERK 7782
15 BOSTON

Natural join

Natural Join also returns matching rows only, this join performance is very high compared to inner join Definition: In this join, you are not required to use join condition explicitly, in this case, resource tables must have a common column, based on this oracle server itself internal establishes join condition

Natural Join Example

select rownum,ename,loc from
emp
NATURAL join 
dept
where loc='CHICAGO';
O/P
ROWNUM ENAME LOC
1 BLAKE CHICAGO
2 ALLEN CHICAGO
3 WARD CHICAGO
4 MARTIN CHICAGO
5 TURNER CHICAGO
6 JAMES CHICAGO

Cross join

Definition: In this type of join data in each and, every row in  one table is added to all the rows in  another table
Here cross product operation  performed

Here we have 14 rows in emp table and 4 rows in dept table hence 14*4=56 records are displayed if the cross join is performed on these tables without condition specified

Cross Join example

select rownum,ename,sal,dname,loc from 
emp 
cross join
dept
where loc='CHICAGO';

O/P

ROWNUMENAME   SAL  
DNAMELOC
1KING5000SALESCHICAGO
2BLAKE2850SALESCHICAGO
3CLARK2450SALESCHICAGO
4JONES2975SALESCHICAGO
5SCOTT3000SALESCHICAGO
6FORD3000SALESCHICAGO
7SMITH800SALESCHICAGO
8ALLEN1600SALESCHICAGO
9WARD1250SALESCHICAGO
10MARTIN1250SALESCHICAGO
11TURNER1500SALESCHICAGO
12ADAMS1100SALESCHICAGO
13JAMES950SALESCHICAGO
14MILLER1300SALESCHICAGO

 

Heera 14 records are displayed because there is one row in dept table with location Chicago and that row is appended to all the rows in emp table

3 comments on “Joins in DBMS”


  • Shubham

    In inner join there should be same name attribute in both tables, but in above emp table there is no loc attribute whereas in dept table it is present then how the query will be execute succesfully?