Join opearation
Simple join or inner join or equi join
SQL> select
s.sid,s.sname,o.orderdate
from
suppliers s,orders o
where
s.sid=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
SQL> select
s.sid,s.sname,o.orderdate
from
suppliers s
join
orders o
on s.sid=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
Outer joins
Left join
SQL> select
s.sid,s.sname,o.orderdate
from suppliers s
left join
orders o
on s.sid=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
44 sony
22 hewlett Packard
SQL> select
s.sid,s.sname,o.orderdate
from
suppliers s,orders o
where
s.sid=o.sid(+);
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
44 sony
22 hewlett Packard
Right join
SQL> select
s.sid,s.sname,o.orderdate
from
suppliers s
right
join
orders o
on s.sid=o.sid;
SID SNAME ORDERDATE
---------- ---------------
---------
11 ibm 07-SEP-09
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
13-DEC-12
SQL> select
s.sid,s.sname,o.orderdate
from
suppliers s , orders o
where
s.sid(+)=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 07-SEP-09
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
13-DEC-12
Note:
In some databases LEFT JOIN is called LEFT OUTER
JOIN,
RIGHT JOIN
is called RIGHT OUTER JOIN.
Full join
SQL>
select s.sid,s.sname,o.orderdate
from
suppliers s
full join
orders o
on s.sid=o.sid;
SID SNAME ORDERDATE
----------
--------------- ---------
11 ibm 12-AUG-10
33 microsoft 09-NOV-11
11 ibm 07-SEP-09
44 sony
22 hewlett packard
13-DEC-12
type of join operation to
perform
Display
names of the employees and their loc.(cartisian product)
Display
names of the employees along their manager names.(self join)
Display
names of the employees who are working
in NEW YORK.(equi join)
Display
names of the employee,salary of each and grade of each .(non equi join)
SQL> select *
from student;
no rows selected
SQL> rollback;
Rollback
complete.
SQL> select *
from student;
ROLLNO NAME AGGR ADDR
----------
-------------------- ---------- ----------
1 akshay 98.5
2 shruthi 97.4
3 venkat 87.6
SQL> drop
table student;
Table dropped.
SQL> create table student(rollno number,name
varchar2(20));
Table created.
SQL> insert
into student values(10,'askhay');
1 row created.
SQL> insert
into student values(20,'ravi');
1 row created.
SQL> select *
from student;
ROLLNO NAME
----------
--------------------
10 askhay
20 ravi
SQL> commit;
Commit complete.
SQL> savepoint
p;
Savepoint
created.
SQL> update
student set name='madhu' where rollno=1;
0 rows updated.
SQL> update
student set name='madhu' where rollno=10;
1 row updated.
SQL> select *
from student;
ROLLNO NAME
----------
--------------------
10 madhu
20 ravi
SQL> rollback
to p;
Rollback
complete.
SQL> select *
from student;
ROLLNO NAME
----------
--------------------
10 askhay
20 ravi