15.Joins,EquiJoins,OuterJoins

SQL

Agenda:

JOINS : Joining tables using columns is called joins

Types of Joins

  1. EQUIJOIN
  2. OUTER JOIN
  3. NON-EQUIJOIN
  4. SELF JOIN

EQUIJOIN: Joinig 2 or more tables and retrieve the information where the data is matched in selected tables.

Syntax : SQL> SELECT table1.col1,
table1.col2,
table2.col1,
table2.col2
FROM table1, table2
WHERE table1.col = table2.col

Sample Data:

Table: Supplier Table: Purchase

Vendor_id Vendor_name PONO Item Qty Price Vendor_Id
——— ———— —————————————
100 Dell 800 lp001 10 15000 100 (foreign key column)
101 HP 802 xp001 15 25000 102

102 HCL

Example: Show purchase information along with vendor name where vendor_id is matched in supplier,pur table

Output : vendor_id vendor_name pono item qty price
—————————————————
100 Dell 800 lp001 10 15000
102 HCL 802 xp001 15 25000

SQL> SELECT s.vendor_id,
s.vendor_name,
p.pono,
p.item,
p.qty,
p.price
FROM supplier s,
purchase p
WHERE s.vendor_id = p.vendor_id;

Example 2: Show supplier,purchase and invoice information where entity is matched

Vendor_id Vendor_name PONO Item Qty Price Vendor_Id InvNo Amt PONO
——— ———— ————————————— ——————–
100 Dell 800 lp001 10 15000 100 771 250000 800
101 HP 801 lp002 15 25000 100 772 350000 802
102 HCL 802 xp001 10 35000 102
———————– ————————————————————————

Output :

Vendor Name PoNO    Item    InvNo   Amt
--------------------------------------------
Dell        800 lp001   771 250000
HCL     802 xp001   772 350000

SQL> SELECT s.vendor_name,
p.pono,
p.item
i.invno,
i.amt
FROM supplier s,
purchase p,
invoice i
WHERE s.vendor_id = p.vendor_id
AND p.pono = i.pono

=================================Coretesian Product ========================
Retrieve the information from more than one table but no join condition is mentioned in where clause

SQL> select * from dept_781;

DEPTNO DEPTNAME

   101 it
   102 sales
   103 finance

SQL> select * from emp_781;

   ENO ENAME             SAL     DEPTNO

   800 ram             12000        101
   801 raj             13000        102
   802 kiran           14000        101

SQL> commit;

Commit complete.

Cortesian Query Equijoin Query

SQL> select d.deptno, select d.deptno,
d.deptname, d.deptname,
e.eno, e.eno,
e.ename e.ename
from dept_781 d, from dept_781 d
emp_781 e; emp_781 e;
WHERE d.deptno = e.deptno

DEPTNO DEPTNAME          ENO ENAME                    DEPTNO DEPTNAME          ENO ENAME

———- ———- ———- ———- ———- ———- ———- ———-
101 it 800 ram 101 it 800 ram
101 it 801 raj 101 it 802 kiran
101 it 802 kiran 102 sales 801 raj

   102 sales             800 ram
   102 sales             801 raj
   102 sales             802 kiran

   103 finance           800 ram
   103 finance           801 raj
   103 finance           802 kiran

From dept table each record is repeated for every emp record.

Outer Join : It is used retrieve the data from the table where the data is not equal
along with the data is equal

Syntax: SQL> select t1.col,
t2.col
from table1 t1,
table2 t2
where t1.col = t2.col (+)

(+) indicates collect the data from table t1 though no records matched with table2

Table: Supplier Table: Purchase

Vendor_id Vendor_name PONO Item Qty Price Vendor_Id
——— ———— —————————————
100 Dell 800 lp001 10 15000 100 (foreign key column)
101 HP

102 HCL

SQL> select S.vendor_id,
s.vendor_name “Supplier Name”,
p.pono,
p.item
p.qty,
p.price
from supplier s,
purchase p
where s.vendor_id = p.vendor_id (+)

Output
Vendor ID Supplier Name PONO ITem Qty Price
—————————————————————
100 Dell 800 lp001 10 15000
101 HP
102 HCL