Joins in SQL

Joins in SQL

 

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

  • Joins  are used to retrieve data from multiple tables
  • In all database, if you are joining n tables then we are using n -1 join condition.

 

Learn more about Joins here on this page.

Joins in SQL
Types of Joins in SQL

SQL  has the following types of join

Equi Join or Inner Join

Based on equality Condition for retrieving data from multiple tables
Here condition columns  must belong to the same data type then only you are allowed to perform an inner join operation

Equi-join Example Query 

select rownum dname,sal
from emp,dept
where emp.deptno=dept.deptno AND dname ='SALES';

Consider two sample tables

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(Equi Join)

ROWNUMDNAME   SAL
1SALES2850
2SALES1600
3SALES1250
4SALES1250
5SALES1500
6SALES950

 

Nonequi joins

Same like inner join, Based on a condition  other than equality condition(<,<=,>,>=,<>,between,–)

select ename,loc from
emp
join 
dept
on emp.deptnowhere loc='CHICAGO';

O/P

ENAMELOC
KINGCHICAGO
CLARKCHICAGO
JONESCHICAGO
SCOTTCHICAGO
FORDCHICAGO
SMITHCHICAGO
ADAMSCHICAGO
MILLERCHICAGO

 

Self-join

  • Self-join is nothing but joining a table with itself
  • This type of join is used when we want to compare two different column values within the same table then we must use self join but here the condition  column must belong to Same data type
  • Before we are using self join we must create table alias names.
  • This alias name must be the different name which is also known as reference names
  • alias Names internally behaves like exact another table

self join SQL Query

select rownum,e1.ename "employees",e2.ename "manager" 
from emp e1,emp e2 
where e1.mgr=e2.empno;

O/P

ROWNUMemployeesmanager
1BLAKEKING
2CLARKKING
3JONESKING
4ALLENBLAKE
5WARDBLAKE
6MARTINBLAKE
7TURNERBLAKE
8JAMESBLAKE
9MILLERCLARK
10SCOTTJONES
11FORDJONES
12ADAMSSCOTT
13SMITHFORD

 

Outer join

  • This join is used to retrieve all rows from one table and matching rows from another table
  • If you want to retrieve nonmatching  rows then we are using join operator (+) within the  join condition (left side or right side) this is called Oracle 8i outer join.

 

Left outer join

The join operator(+) is added at the left  side  table of the join condition so that all records from the left side and matching records from the right table are  displayed

Left Join SQL query

select rownum,ename,sal,d.deptno,dname,loc 
from emp e,dept d 
where e.deptno(+)=d.deptno

O/P

ROWNUMENAMESALDEPTNODNAMELOC
1KING500010ACCOUNTINGNEW YORK
2BLAKE285030SALESCHICAGO
3CLARK245010ACCOUNTINGNEW YORK
4JONES297520RESEARCHDALLAS
5SCOTT300020RESEARCHDALLAS
6FORD300020RESEARCHDALLAS
7SMITH80020RESEARCHDALLAS
8ALLEN160030SALESCHICAGO
9WARD125030SALESCHICAGO
10MARTIN125030SALESCHICAGO
11TURNER150030SALESCHICAGO
12ADAMS110020RESEARCHDALLAS
13JAMES95030SALESCHICAGO
14MILLER130010ACCOUNTINGNEW YORK
1540OPERATIONSBOSTON

 

Right outer join

Join operator(+) is appended at the right side table of the join condition so that all records from the right side table and matched records from right side table are  displayed

Right join SQL query

select rownum,ename,sal,d.deptno,dname,loc 
from emp e,dept d 
where e.deptno=d.deptno(+);

O/P

ROWNUMENAMESALDEPTNODNAMELOC
1KING500010ACCOUNTINGNEW YORK
2CLARK245010ACCOUNTINGNEW YORK
3MILLER130010ACCOUNTINGNEW YORK
4JONES297520RESEARCHDALLAS
5SCOTT300020RESEARCHDALLAS
6FORD300020RESEARCHDALLAS
7SMITH80020RESEARCHDALLAS
8ADAMS110020RESEARCHDALLAS
9BLAKE285030SALESCHICAGO
10ALLEN160030SALESCHICAGO
11WARD125030SALESCHICAGO
12MARTIN125030SALESCHICAGO
13TURNER150030SALESCHICAGO
14JAMES95030SALESCHICAGO

 

Full outer join

  • Prior to Oracle 9i if you want to retrieve both matching and unmatched rows as well, can we perform Union operation between left and right joins
  • so that matched rows from two tables with the join condition are satisfied, and unmatched rows from the left side table, as well as a right side table, also displayed.

Full Join  SQL query

select rownum,ename,sal,d.deptno,dname,loc 
from emp e,dept d 
where e.deptno(+)=d.deptno ;
union 
select rownum,ename,sal,d.deptno,dname,loc 
from emp e,dept d 
where e.deptno=(+)d.deptno;

O/P

ROWNUMENAME  SAL 
DEPTNODNAMELOC
1KING500010ACCOUNTINGNEW YORK
2BLAKE285030SALESCHICAGO
3CLARK245010ACCOUNTINGNEW YORK
4JONES297520RESEARCHDALLAS
5SCOTT300020RESEARCHDALLAS
6FORD300020RESEARCHDALLAS
7SMITH80020RESEARCHDALLAS
8ALLEN160030SALESCHICAGO
9WARD125030SALESCHICAGO
10MARTIN125030SALESCHICAGO
11TURNER150030SALESCHICAGO
12ADAMS110020RESEARCHDALLAS
13JAMES95030SALESCHICAGO
14MILLER130010ACCOUNTINGNEW YORK
1540OPERATIONSBOSTON