Joins

Join :SQL JOINS are used to retrieve data from multiple tables i.e two  or  more  tables  and  combine  them  into  one  table .Joins are of two types

1.ANSI FORMAT JOIN(New Style Format)

with ON keyword join condition

• INNER JOIN : 
• OUTER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN
• NATURAL JOIN

1) INNER JOIN/SIMPLE JOIN

Inner Join is the simplest and most common type of join. It is also known as simple join. 

It retrieves  the data from multiple tables based on equality condition 

common column should be there in both the tables then only we can perform the inner join concept

Example 1:

SQL> create table std(sno number(2),sname varchar(8),saddress varchar(5));
Table created.
SQL> insert into std values (11,'raju','vsp');
1 row created.
SQL> insert into std values (22,'rani','hyd');
1 row created.
SQL> insert into std values (33,'vani','hyd');
1 row created.
SQL> insert into std values (44,'dhoni','bnc');
1 row created.
SQL> select * from std;
       SNO SNAME    SADDR
---------- -------- -----
        11 raju     vsp
        22 rani     hyd
        33 vani     hyd
        44 dhoni    bnc

SQL> create table dept(dno varchar(11),dname varchar(8),sno number(2));

SQL> insert into dept values ('d1','cse',11);

1 row created.
SQL> insert into dept values ('d2','it',22);
1 row created.
SQL> insert into dept values ('d3','ece',44);
1 row created.

SQL> select * from dept;

DNO         DNAME           SNO

----------- -------- ----------

d1          cse              11

d2          it                 22

d3          ece              44

SQL> select * from std,dept ;

 SNO   SNAME    SADDR DNO    DNAME      SNO

---------- -------- ----- ----------- -------- ----------

        11 raju     vsp   d1          cse              11

        22 rani     hyd   d1          cse              11

        33 vani     hyd   d1          cse              11

        44 dhoni    bnc   d1          cse              11

        11 raju     vsp   d3          ece              44

        22 rani     hyd   d3          ece              44

        33 vani     hyd   d3          ece              44

        44 dhoni    bnc   d3          ece              44

        11 raju     vsp   d2          it               22

        22 rani     hyd   d2          it               22

        33 vani     hyd   d2          it               22

       SNO SNAME    SADDR DNO         DNAME           SNO

---------- -------- ----- ----------- -------- ----------

        44 dhoni    bnc   d2          it               22

12 rows selected.

SQL> select * from std inner join dept on std.sno=dept.sno ;

  SNO     SNAME  SADDR DNO    DNAME      SNO

---------- -------- ----- ----------- -------- ----------

        11 raju     vsp   d1          cse              11

        22 rani     hyd   d2          it               22

        44 dhoni    bnc   d3          ece              44

Example 2:

course Table:

create table course(cid number(3) primary key,cname varchar2(15) not null);

student Table:

create table student(sid number(2) primary key,sname varchar2(15) not null,cid number(3),constraint fk_student foreign key(cid) references course(cid));

SQL> desc course;

Name Null? Type

--------------------------    ------------    ------------

CID                                  NOT NULL   NUMBER(3)

CNAME                             NOT NULL   VARCHAR2(15)

SQL> desc students;

Name Null? Type

---------------------------    --------    ------------

SID                              NOT NULL     NUMBER(2)

SNAME                         NOT NULL     VARCHAR2(15)

CID                                                 NUMBER(3)

Inserting Data into COURSE table:

insert into course values(111,'MPC');

insert into course values(222,'BIPC');

insert into course values(333,'CEC');

insert into course values(444,'HEC');

insert into course values(555,'MEC');

SQL> SELECT * FROM COURSE;

CID         CNAME

---------- ---------------

111         MPC

222         BIPC

333         CEC

444         HEC

555         MEC

Inserting Data into STUDENTS table:

insert into STUDENTS values(11,'RAJESH',444);

insert into STUDENTS values(22,'VIJAY',555);

insert into STUDENTS values(33,'KAMAL',111);

insert into STUDENTS values(44,'GANESH',222);

insert into STUDENTS values(55,'RUPESH',333);

insert into STUDENTS(SID,SNAME) values(66,'VENKAT');

insert into STUDENTS(SID,SNAME) values(77,'SURESH');

insert into students values(88,'MURTY',555);

insert into students values(99,'ROOPA',222);

insert into students values(98,'RAMYA',333);

SQL> SELECT * FROM STUDENTS;

SID         SNAME             CID

---------- --------------- ----------

11             RAJESH           444

22             VIJAY              555

33             KAMAL            111

44             GANESH          222

55             RUPESH          333

66             VENKAT

77             SURESH

88             MURTY         555

99             ROOPA         222

98             RAMYA         333

SQL>select * from students s inner join course c on s.cid=c.cid;

SID         SNAME         CID         CID         CNAME

----- --------------- ---------- ---------- ---------------

33         KAMAL           111         111         MPC

44         GANESH         222         222         BIPC

55         RUPESH          333         333         CEC

11         RAJESH          444         444         HEC

22         VIJAY             555         555         MEC

How to Inserting entire Data into COURSE and students table using notepad :

2.OUTER JOIN : To  see  the  data  from  one  table  even  if  there  is  no  corresponding   row  in  the  joining  table ,  outer join  mechanism  is  used . such  rows  can  forcefully  selected  by  using  the  outer  join  

SQL> select * from std ;

  SNO    SNAME    SADDR

---------- -------- -----

        11 raju     vsp

        22 rani     hyd

        33 vani     hyd

        44 dhoni    bnc

SQL> select * from dept ;

DNO         DNAME           SNO

----------- -------- ----------

d1          cse              11

d3          ece              44

d2          it               22

SQL> select sname,dno,dname from std right outer join dept on std.sno=dept.sno ;

SNAME    DNO         DNAME

-------- ----------- --------

raju     d1          cse

rani     d2          it

dhoni    d3          ece

SQL> select sname,dno,dname from std left outer join dept on std.sno=dept.sno ;

SNAME    DNO         DNAME

-------- ----------- --------

raju     d1          cse

dhoni    d3          ece

rani     d2          it

vani

SQL> select sname,dno,dname from std full outer join dept on std.sno=dept.sno ;

SNAME    DNO         DNAME

-------- ----------- --------

raju        d1          cse

dhoni     d3          ece

rani       d2          it

vani

Example 2:

Left Outer Join

Left Outer Join returns all rows from the left (first) table specified in the ON condition and only those rows from the right (second) table where the join condition is met.

select * from students S LEFT JOIN course C ON S.CID=C.CID;

SID         SNAME         CID         CID         CNAME

----- --------------- ---------- ---------- ---------------

33     KAMAL                   111         111         MPC

44     GANESH                 222         222         BIPC

55     RUPESH                 333         333         CEC

11     RAJESH                  444         444         HEC

22     VIJAY                     555         555         MEC

77     SURESH

66     VENKAT

Right Outer Join

The Right Outer Join returns all rows from the right-hand table specified in the ON condition and only those rows from the other table where the join condition is met.

SQL> select * from students S RIGHT JOIN course C ON S.CID=C.CID;

SID             SNAME         CID         CID         CNAME

---------- --------------- ---------- ---------- ---------------

11             RAJESH         444         444         HEC

22             VIJAY            555         555         MEC

33             KAMAL          111         111          MPC

44             GANESH        222         222         BIPC

55             RUPESH         333         333        CEC

Full Outer Join

The Full Outer Join returns all rows from the left hand table and right hand table. It places NULL where the join condition is not met.

SQL> select * from students S FULL JOIN course C ON S.CID=C.CID;

SID             SNAME         CID         CID         CNAME

---------- --------------- ---------- ---------- ---------------

11             RAJESH             444     444         HEC

22             VIJAY                555     555         MEC

33             KAMAL              111     111         MPC

44             GANESH            222     222         BIPC

55             RUPESH            333      333         CEC

66             VENKAT

77             SURESH

3)CROSS JOIN (Cartesian Products)

The CROSS JOIN specifies that all rows from first table join with all of the rows of second table. If there are "x" rows in table1 and "y" rows in table2 then the cross join result set have x*y rows.

Example:

SQL> select * from std,dept ;

SNO SNAME    SADDR DNO         DNAME           SNO

---------- -------- ----- ----------- -------- ----------

        11 raju     vsp   d1          cse              11

        22 rani     hyd   d1          cse              11

        33 vani     hyd   d1          cse              11

        44 dhoni    bnc   d1          cse              11

        11 raju     vsp   d3          ece              44

        22 rani     hyd   d3          ece              44

        33 vani     hyd   d3          ece              44

        44 dhoni    bnc   d3          ece              44

        11 raju     vsp   d2          it               22

        22 rani     hyd   d2          it               22

        33 vani     hyd   d2          it               22

  SNO   SNAME    SADDR DNO         DNAME           SNO

---------- -------- ----- ----------- -------- ----------

        44 dhoni    bnc   d2          it               22

12 rows selected.

SQL> select * from std cross join dept ;

 SNO     SNAME    SADDR DNO         DNAME           SNO

---------- -------- ----- ----------- -------- ----------

        11 raju     vsp   d1          cse              11

        22 rani     hyd   d1          cse              11

        33 vani     hyd   d1          cse              11

        44 dhoni    bnc   d1          cse              11

        11 raju     vsp   d3          ece              44

        22 rani     hyd   d3          ece              44

        33 vani     hyd   d3          ece              44

        44 dhoni    bnc   d3          ece              44

        11 raju     vsp   d2          it               22

        22 rani     hyd   d2          it               22

        33 vani     hyd   d2          it               22

       SNO SNAME    SADDR DNO         DNAME           SNO

---------- -------- ----- ----------- -------- ----------

        44 dhoni    bnc   d2          it               22

12 rows selected.

Example 2:

select * from students,course;

or

select * from students cross join course;

4) Natural Join

A type of Equi Join which occurs implicitly by comparing all the same name columns in both tables and eliminates the duplicate attributes.

This will perform an Inner Join by default.

Example 1:
SQL> select * from std  ;
       SNO SNAME    SADDR
---------- -------- -----
        11 raju     vsp
        22 rani     hyd
        33 vani     hyd
        44 dhoni    bnc

SQL> select * from dept  ;
DNO         DNAME     SNO
----------- -------- ----------
d1          cse              11
d3          ece              44
d2          it                 22

SQL> select sname,dname from std t1,dept t2 ;

SNAME    DNAME

-------- --------

raju     cse

rani     cse

vani     cse

dhoni    cse

raju     ece

rani     ece

vani     ece

dhoni    ece

raju     it

rani     it

vani     it

SNAME    DNAME

-------- --------

dhoni    it

12 rows selected.

SQL> select sname,dname from std t1,dept t2 where t1.sno=t2.sno;

SNAME    DNAME

-------- --------

raju     cse

rani     it

dhoni    ece

SQL> select sname,dname from std natural join dept;

SNAME    DNAME

-------- --------

raju     cse

rani     it

dhoni    ece

Example 2:

SQL> select * from students natural join course;

CID             SID         SNAME        CNAME

---------- ---------- --------------- ----------

111             33             KAMAL           MPC

222             44             GANESH         BIPC

333             55             RUPESH         CEC

444             11             RAJESH         HEC

555             22             VIJAY             MEC

2.NON-ANSI FORMAT JOIN (Old Style Format)

with WHERE keyword join condition

• THETA JOIN
• EQUI JOIN
• NON-EQUI JOIN
• SELF JOIN

1)Theta Join:

If we make a query for join using any relational operator,(e.g., =,!=,<, >, >=,<=), then that join query is considered as Theta join(Non-Equi Join or Equi).

Equi Join

A Theta Join is an Equi join if it uses only "=" operator in select query Oracle Equi join returns the matching column values of the associated tables. It uses a comparison operator in the WHERE clause to refer equality.

Example1:

SQL> select * from std;
       SNO SNAME    SADDR
---------- -------- -----
        11 raju     vsp
        22 rani     hyd
        33 vani     hyd
        44 dhoni    bnc
SQL> select * from dept;
DNO         DNAME           SNO
----------- -------- ----------
d1          cse              11
d2          it               22
d3          ece              44
SQL> select * from std,dept;
       SNO SNAME    SADDR DNO         DNAME           SNO
---------- -------- ----- ----------- -------- ----------
        11 raju     vsp   d1          cse              11
        11 raju     vsp   d2          it               22
        11 raju     vsp   d3          ece              44
        22 rani     hyd   d1          cse              11
        22 rani     hyd   d2          it               22
        22 rani     hyd   d3          ece              44
        33 vani     hyd   d1          cse              11
        33 vani     hyd   d2          it               22
        33 vani     hyd   d3          ece              44
        44 dhoni    bnc   d1          cse              11
        44 dhoni    bnc   d2          it               22
       SNO SNAME    SADDR DNO         DNAME           SNO
---------- -------- ----- ----------- -------- ----------
        44 dhoni    bnc   d3          ece              44
12 rows selected.
SQL> select * from std t1,dept t2 where t1.sno=t2.sno ;
       SNO SNAME    SADDR DNO         DNAME           SNO
---------- -------- ----- ----------- -------- ----------
        11 raju     vsp   d1          cse              11
        22 rani     hyd   d2          it               22
        44 dhoni    bnc   d3          ece              44
SQL> select * from std t1,dept t2 where t1.sno!=t2.sno ;
       SNO SNAME    SADDR DNO         DNAME           SNO
---------- -------- ----- ----------- -------- ----------
        22 rani     hyd   d1          cse              11
        33 vani     hyd   d1          cse              11
        44 dhoni    bnc   d1          cse              11
        11 raju     vsp   d2          it               22
        33 vani     hyd   d2          it               22
        44 dhoni    bnc   d2          it               22
        11 raju     vsp   d3          ece              44
        22 rani     hyd   d3          ece              44
        33 vani     hyd   d3          ece              44
9 rows selected.

Example2:

select * from students s,course c where s.cid=c.cid;

Non-Equi Join

Example 1:

SQL> select sname,dname from std t1,dept t2 where t1.sno!=t2.sno ;
SNAME    DNAME
-------- --------
rani     cse
vani     cse
dhoni    cse
raju     it
vani     it
dhoni    it
raju     ece
rani     ece
vani     ece
9 rows selected.
SQL> select sname,dname from std t1,dept t2 where t1.sno<>t2.sno ;
SNAME    DNAME
-------- --------
rani     cse
vani     cse
dhoni    cse
raju     it
vani     it
dhoni    it
raju     ece
rani     ece
vani     ece
9 rows selected.

Example 2:

select * from students s,course c where s.cid>c.cid;

select * from students s,course c where s.cid<c.cid;

select * from students s,course c where s.cid!=c.cid;

2)SELF JOIN

Self Join is a specific type of Join. In Self Join, a table is joined with itself (Unary relationship). A self join simply specifies that each rows of a table is combined with itself and every other row of the table.

Example 1:

SQL> create table study(sid varchar(5),cid varchar(5),year number(4));

Table created.

SQL> insert into study values('s1','c1',2016);

1 row created.

SQL> insert into study values('s2','c2',2017);

1 row created.

SQL> insert into study values('s1','c2',2017);

1 row created.

SQL> select * from study;

SID   CID         YEAR

----- ----- ----------

s1    c1          2016

s2    c2          2017

s1    c2          2017

SQL> select t1.sid from study t1,study t2 where t1.sid = t2.sid and t1.cid <> t2.cid;

SID

-----

s1

s1

SQL> select t1.sid from study t1,study t2 where t1.sid = t2.sid and t1.cid != t2.cid;    // t1.cid <!=>  or <> t2.cid;

SID

-----

s1

s1

SQL> select * from study t1,study t2 where t1.sid = t2.sid and t1.cid != t2.cid;

SID   CID         YEAR SID   CID         YEAR

----- ----- ---------- ----- ----- ----------

s1    c2          2017 s1    c1          2016

s1    c1          2016 s1    c2          2017

Example 2:

SQL> select * from emp;

EMP_ID     ENAME             SAL     DEPT_ID     MGR

---------- --------------- - --------- ---------- ----------

1                 raj                 234567         10         26

3                 vimal             364567         30         27

7                 kamal             64567          30         62

8                 guru               684567         30

12               ramesh           184567         80         62

62               surya               834567        90         27

26               yamuna           374567         90         1

27               amruta             274567        90          7

select t1.ename employee,t2.ename manager from emp t1,emp t2 where t2.emp_id=t1.mgr order by manager;

EMPLOYEE MANAGER

--------------- -------

surya amruta

vimal amruta

amruta guru

yamuna raj

ramesh surya

kamal surya

raj yamuna