SQL Server EXISTS

The "EXISTS" condition is used to test the existence of specific record in a sub query.

Syntax:

    WHERE EXISTS ( subquery );  

The sub query also is a SELECT statement .The EXISTS operator returns true if the sub query returns one or multiple records.

Here , we have created a table and inserted data to the table for example purpose.

CREATE TABLE student

(

         id INT NOT NULL,

         name VARCHAR (500) NOT NULL,

         age INT NOT NULL,

         adress VARCHAR (500) NOT NULL,

         date_of_birth DATETIME NOT NULL,

         height DECIMAL(10,2) NOT NULL

)

INSERT INTO student(id,name,age,adress,date_of_birth,height)

SELECT 1,'John1',12,'34,student street','01/08/2018',10.5

UNION

SELECT 1,'John2',13,'35,student street','02/08/2018',11

UNION

SELECT 1,'John3',14,'36,student street','03/08/2018',11.5

UNION

SELECT 1,'John4',15,'37,student street','04/08/2018',12

UNION

SELECT 1,'John5',16,'38,student street','05/08/2018',12.5

UNION

SELECT 1,'John6',17,'39,student street','06/08/2018',13.5

UNION

SELECT 1,'John7',18,'40,student street','07/08/2018',14

UNION

SELECT 1,'John8',19,'41,student street','08/08/2018',14.5

UNION

SELECT 1,'John9',20,'42,student street','09/08/2018',15

UNION

SELECT 1,'John10',21,'43,student street','10/08/2018',15.5

UNION

SELECT 1,'John11',22,'44,student street','11/08/2018',16

How EXISTS query is written

SELECT Column1,Column2,Column3..

FROM table_name

WHERE EXISTS

      (SELECT column_name FROM table_name WHERE condition);

  

Example of exists condition . Query return height more than 15.

SELECT a.name, 

       a.age, 

       a.adress,

       a.date_of_birth

FROM student a

WHERE EXISTS

(SELECT b.age FROM student b WHERE b.height>15 and a.age=b.age);

name     age   adress                date_of_birth

John10    21    43,student street    2018-10-08 00:00:00.000
John11    22    44,student street    2018-11-08 00:00:00.000