Introduced with the keyword EXISTS, the subquery functions as an existence test. The WHERE clause of the outer query tests whether the rows that are returned by the subquery exist. subquery introduced with EXISTS has the following syntax:
IF EXISTS
(
SELECT
*FROM ajay
WHERE empid = 12
)
BEGIN
print ‘Valid Employee’
END
ELSE
print ‘Invalid Employee’
OUTPUT:‘Invalid Employee’
The keyword is NOT EXISTS works the opposite as EXISTS. The WHERE clause in NOT EXISTS is satisfied if no rows are returned by the subquery. In This example finds the Empid of Ajay_Table who do not match in Empid of Raj_table.
select *from ajay
select *from raj
select *from raj r2
where
not exists
(
select
*from ajay a1
where a1.empid=r2.empid
)
select *from raj r2
where
exists
(
select
*from ajay a1
where a1.empid=r2.empid
)