Joins in SQL

July 23, 2019

  • 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

SQL  has the following types of join

Equijoin 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

Equijoin Example Query 

select rownum dname,sal
from emp,dept
where emp.deptno=dept.deptno AND dname ='SALES';
joins in SQL
Types of Joins
Consider two sample tables 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
O/P(Equi Join)
ROWNUM DNAME SAL
1 SALES 2850
2 SALES 1600
3 SALES 1250
4 SALES 1250
5 SALES 1500
6 SALES 950

Nonequi joins

Same like inner join, Based on a condition  other than equality condition(<,<=,>,>=,<>,between,–) 
select ename,loc from
emp
join 
dept
on emp.deptno<dept.deptno
where loc='CHICAGO';

O/P

ENAME LOC
KING CHICAGO
CLARK CHICAGO
JONES CHICAGO
SCOTT CHICAGO
FORD CHICAGO
SMITH CHICAGO
ADAMS CHICAGO
MILLER CHICAGO

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
ROWNUM employees manager
1 BLAKE KING
2 CLARK KING
3 JONES KING
4 ALLEN BLAKE
5 WARD BLAKE
6 MARTIN BLAKE
7 TURNER BLAKE
8 JAMES BLAKE
9 MILLER CLARK
10 SCOTT JONES
11 FORD JONES
12 ADAMS SCOTT
13 SMITH FORD

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
ROWNUM ENAME SAL DEPTNO DNAME LOC
1 KING 5000 10 ACCOUNTING NEW YORK
2 BLAKE 2850 30 SALES CHICAGO
3 CLARK 2450 10 ACCOUNTING NEW YORK
4 JONES 2975 20 RESEARCH DALLAS
5 SCOTT 3000 20 RESEARCH DALLAS
6 FORD 3000 20 RESEARCH DALLAS
7 SMITH 800 20 RESEARCH DALLAS
8 ALLEN 1600 30 SALES CHICAGO
9 WARD 1250 30 SALES CHICAGO
10 MARTIN 1250 30 SALES CHICAGO
11 TURNER 1500 30 SALES CHICAGO
12 ADAMS 1100 20 RESEARCH DALLAS
13 JAMES 950 30 SALES CHICAGO
14 MILLER 1300 10 ACCOUNTING NEW YORK
15 – – 40 OPERATIONS BOSTON

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
ROWNUM ENAME SAL DEPTNO DNAME LOC
1 KING 5000 10 ACCOUNTING NEW YORK
2 CLARK 2450 10 ACCOUNTING NEW YORK
3 MILLER 1300 10 ACCOUNTING NEW YORK
4 JONES 2975 20 RESEARCH DALLAS
5 SCOTT 3000 20 RESEARCH DALLAS
6 FORD 3000 20 RESEARCH DALLAS
7 SMITH 800 20 RESEARCH DALLAS
8 ADAMS 1100 20 RESEARCH DALLAS
9 BLAKE 2850 30 SALES CHICAGO
10 ALLEN 1600 30 SALES CHICAGO
11 WARD 1250 30 SALES CHICAGO
12 MARTIN 1250 30 SALES CHICAGO
13 TURNER 1500 30 SALES CHICAGO
14 JAMES 950 30 SALES CHICAGO

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
ROWNUM ENAME SAL DEPTNO DNAME LOC
1 KING 5000 10 ACCOUNTING NEW YORK
2 BLAKE 2850 30 SALES CHICAGO
3 CLARK 2450 10 ACCOUNTING NEW YORK
4 JONES 2975 20 RESEARCH DALLAS
5 SCOTT 3000 20 RESEARCH DALLAS
6 FORD 3000 20 RESEARCH DALLAS
7 SMITH 800 20 RESEARCH DALLAS
8 ALLEN 1600 30 SALES CHICAGO
9 WARD 1250 30 SALES CHICAGO
10 MARTIN 1250 30 SALES CHICAGO
11 TURNER 1500 30 SALES CHICAGO
12 ADAMS 1100 20 RESEARCH DALLAS
13 JAMES 950 30 SALES CHICAGO
14 MILLER 1300 10 ACCOUNTING NEW YORK
15 – – 40 OPERATIONS BOSTON