Agenda:
JOINS : Joining tables using columns is called joins
Types of Joins
- EQUIJOIN
- OUTER JOIN
- NON-EQUIJOIN
- 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 350000SQL> 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 financeSQL> select * from emp_781;
ENO ENAME SAL DEPTNO 800 ram 12000 101
801 raj 13000 102
802 kiran 14000 101SQL> 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 kiranFrom 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
