5 Types of SQL Joins Explained with Examples

 

SQL JOINS

It is  Data Base Management System(DBMS) concept in which is used to get the information from two or more tables.

Types of Joins:

1. Inner/equi join.
2. Outer join: Left outer join, Right outer join, Full outer join.
3. Self join.

SQL Joins

Inner Join/Equi Join:

when you use inner/equi join ,then only matching records between both the table will display.

select cname, email_id, dob from customer cust, orders ord where cust.cid=ord.cid;

SQL> select cname,email_id,dob from customer cust, orders ord where cust.cid=ord.cid;

Left Outer Join:

When you apply left outer join on two table Customer and Orders then query return N matching records from both the table & remaining records in left side table(i.e table Customer).
  
select cname, total_qty, total_price from customer c, orders o where c.cid=o.cid(+);

SQL> select cname, total_qty, total_price from customer c, orders o where c.cid=o.cid(+);

CNAME                TOTAL_QTY                     TOTAL_PRICE
---------------        ----------                         -----------
rakesh kumar           3                                 200
rajiv shyam              2                                 350
sunil jha                   7                                 150
vijay goal                 2                                 180
vijay goal                 2                                 180
raman shyam

select cname,total_qty,total_price from customer c left outer join  orders o 
on c.cid=o.cid;

Right Outer Join:

When you apply left outer join on two table A and B then query return N matching records from both the table & remaining records in right side table(i.e table Orders).

insert into orders values(99,'12-may-2004',02,350,1002,'ACTIVE');  

select cname,total_qty,total_price from customer c, orders o
where c.cid(+)=o.cid;

SQL> select cname,total_qty,total_price from customer c, orders o where c.cid(+)=o.cid;

select cname,total_qty,total_price from customer c right outer join  orders o 
on c.cid=o.cid;

SQL> select cname,total_qty,total_price from customer c right outer join  orders o 
on c.cid=o.cid;

insert into orders values(99,'12-may-2004',02,350,1002,'ACTIVE');

Full Outer Join:

when we apply full outer joins on table Customer & Orders then it gives the matching records from both the table Customer & Orders, remaining records from left side table i.e Customer, remaining records in right side table i.e Orders. 

Full outer Join= Inner Joins + Outer Joins.

select cname,total_qty,total_price from customer c, orders o where c.cid(+)=o.cid(+);

SQL> select cname, total_qty, total_price from customer c, orders o where c.cid(+)=o.cid(+) where c.cid(+)=o.cid(+).

*ERROR at line 3:
ORA-01468: a predicate may reference only one outer-joined table.

from customer c full outer join  orders o on c.cid=o.cid; select cname, total_qty, total_price.

SQL> select cname,total_qty,total_price from customer c full outer join  orders o on c.cid=o.cid;

CNAME               TOTAL_QTY               TOTAL_PRICE
---------------     ---------------             --------------
rakesh kumar            3                            200
rajiv shyam               2        350
sunil jha                    7          150
vijay goal                  2          180
vijay goal                  2          180
rajiv shyam               2          350
raman shyam

Self Join:

In self join only one table will be there ,the same table will be join to itself.

SQL> select * from empl;

     EMPID         ENAME               SAL            MGRID
----------         ----------            ----------    ----------
         1             a                    2000            3
         2             b                    3999            3
         3             c                    1000            5
         4             d                    222              6

select emp.ename,mgr.ename from empl emp,empl mgr
where mgr.empid=emp.mgrid;

SQL> select emp.ename,mgr.ename from empl emp,empl mgr where mgr.empid=emp.mgrid;

ENAME         ENAME
-------        ----------
     a             c
     b             c

insert into empl values(05,'e',111,04);
insert into empl values(06,'f',199,03);

SQL> select emp.ename,mgr.ename from empl emp,empl mgr where mgr.empid=emp.mgrid;

ENAME           NAME
----------    ----------
a          c
b                   c
c          e
d                   f
e          d
f          c

6 rows selected.

Thanks for reading, for more updates follow our Youtube channel.

Follow on YouTube