The SQL EXISTS operator is used in combination with a OuterQuery and Subquery and is considered to be met, if the subquery returns at least one row. We can use it in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax 

OuterQuery EXISTS (Subquery)
Syntax description
OuterQuery get executed first, and for each record of outer query, Subquery will get executed. If subquery find and return at least one record for OuterQuery then it meet the EXISTS condition.

Lets' prepare some data to understand it with an example 
CREATE TABLE TblStudent
(
    SRNo VARCHAR(15) NOT NULL PRIMARY KEY,
    StudentName VARCHAR(50),
    FatherName VARCHAR(50),
    CourseCode VARCHAR(15),
    PaidFee INT,
    IsAllFeePaid VARCHAR(10) DEFAULT 'NA'
)

GO

INSERT INTO TblStudent(SRNo, StudentName, FatherName, CourseCode,PaidFee) 
VALUES ('SR01','Kamal','Harish','BCA',50000),
('SR02','Sachin','Gagan','BBA',60000),
('SR03','Kapil','Raj','MCA',0),
('SR04','Tanuj','Sanjeev','BPharma',0),
('SR05','Lalit','Shyam','B.Arch',0)


GO

CREATE TABLE TblCourse
(
    CourseCode VARCHAR(15) NOT NULL PRIMARY KEY,
    CourseName VARCHAR(50),
    CourseFee INT
)

GO

INSERT INTO TblCourse(CourseCode,CourseName,CourseFee) VALUES
('BCA','Bachelor of Computer Applications',50000),
('BBA','Bachelors of Business Administration',60000),
('MCA','Master of Computer Applications' ,85000)
1. The EXISTS operator evaluates as true even when the subquery returns NULL.
SELECT StudentName,FatherName 
FROM TblStudent 
WHERE EXISTS (SELECT NULL)
How the above query will get executed
The outer query will get executed first, then for each record of outer query result set inner query will get executed and return at least one record with NULL value. So the above query will populate all records from TblStudent because the inner query satisfies EXISTS condition for all records of outer query. Result
2. Using EXISTS operator with the SELECT Statement
SELECT StudentName,FatherName,CourseCode
FROM TblStudent 
WHERE EXISTS
(
	SELECT TblCourse.CourseCode 
	FROM TblCourse WHERE TblCourse.CourseCode=TblStudent.CourseCode
)
How the above query will get executed
For each record of outer query result set, inner query will satisfy the EXISTS condition only for "BBA", "BCA", and "MCA" Courses. So above query will populate only three records from TblStudent table. Result
3. Using NOT with the EXISTS operator
SELECT StudentName,FatherName,CourseCode
FROM TblStudent 
WHERE NOT EXISTS
(
	SELECT TblCourse.CourseCode 
	FROM TblCourse WHERE TblCourse.CourseCode=TblStudent.CourseCode
)
It will populate 2 records from the tblStudent table whose courseCode is not defined in the tblCourse table. Result
4. Using EXISTS operator with the UPDATE Statement
UPDATE TblStudent 
SET IsAllFeePaid='Yes'
WHERE EXISTS
(
	SELECT TblCourse.CourseCode 
	FROM TblCourse WHERE TblStudent.PaidFee>=TblCourse.CourseFee
)
The above query will update only those records in TblStudent table whose PaidFee >= CourseFee in TblCourse table. Result: Only two yellow highlighted records updated.
5. Using EXISTS operator with the DELETE Statement
DELETE
FROM TblStudent 
WHERE EXISTS
(
	SELECT TblCourse.CourseCode 
	FROM TblCourse WHERE TblCourse.CourseCode=TblStudent.CourseCode
)
above query will delete 3 records given in below image