Exists:
-------------
The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
Ex:
---
SELECT *
FROM dept
WHERE EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
--------------------------------------
Not Exists:
SELECT *
FROM dept
WHERE NOT EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
-------------------------------------
Merge:
create table student10 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student10 values ( 101, 'arun',30);
insert into student10 values ( 102, 'anil',40);
insert into student10 values ( 103, 'kiran',50);
create table student20 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student20 values ( 101, 'JOHN',30);
insert into student20 values ( 105, 'SMITH',50);
merge into student10 s1
using student20 s2
on ( s1.sno = s2.sno)
when matched
then update set sname=s2.sname, marks = s2.marks
when not matched
then insert (sno,sname,marks ) values (s2.sno,s2.sname,s2.marks);
-------------
The EXISTS condition is considered "to be met" if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
Ex:
---
SELECT *
FROM dept
WHERE EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
--------------------------------------
Not Exists:
SELECT *
FROM dept
WHERE NOT EXISTS
(select *
from emp
where dept.deptno = emp.deptno);
-------------------------------------
Merge:
create table student10 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student10 values ( 101, 'arun',30);
insert into student10 values ( 102, 'anil',40);
insert into student10 values ( 103, 'kiran',50);
create table student20 ( sno number(3),
sname varchar2(20),
marks number(3));
insert into student20 values ( 101, 'JOHN',30);
insert into student20 values ( 105, 'SMITH',50);
merge into student10 s1
using student20 s2
on ( s1.sno = s2.sno)
when matched
then update set sname=s2.sname, marks = s2.marks
when not matched
then insert (sno,sname,marks ) values (s2.sno,s2.sname,s2.marks);
0 comments:
Post a Comment