Merge, Exists and Non-Exists in PL/SQL

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);