EXISTS

Use of EXISTS and NOT EXISTS in SQL Server

SQL EXISTS condition are used for combination of sub queries and its sub query returns at least one row.

SQL EXISTS condition can be used in a SELECT, INSERT, UPDATE or DELETE statement.


  1. Query Syntax Exists :    WHERE  EXISTS (--subquery);
  2. Query Syntax Not Exists :    WHERE  NOT EXISTS (--subquery);

The Live Query example code as given below

--CREATE TABLE MOBILE

CREATE TABLE [dbo].[Mobile](
            [UID] [varchar](30) NOT NULL,
            [Account] [varchar](30) NOT NULL,
            [Mobile] [char](10) NOT NULL,
            [AlertToMobile] [char](10) NULL,
            [AlertToEmail] [varchar](255) NULL,
 CONSTRAINT [PK_Mobile] PRIMARY KEY CLUSTERED
(
            [UID] ASC,
            [Mobile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


--CREATE TABLE USER

CREATE TABLE [dbo].[User](
            [UID] [varchar](30) NOT NULL,
            [ContactName] [varchar](255) NOT NULL,
            [ContactEmail] [varchar](255) NOT NULL,
            [ContactMobile] [char](10) NULL,
 CONSTRAINT [PK_User_1] PRIMARY KEY CLUSTERED
(
            [ContactEmail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--INSERT TABLE USER

Insert into [USER](UID, ContactName, ContactEmail, ContactMobile) values('001', 'Anil', 'anil@code-sample.com','9015519999')
Insert into [USER](UID, ContactName, ContactEmail, ContactMobile) values('002', 'Sunil', 'Sunil@code-sample.com','9015500999')
Insert into [USER](UID, ContactName, ContactEmail, ContactMobile) values('003', 'Sushil', 'Sushil@code-sample.com','9010019999')
Insert into [USER](UID, ContactName, ContactEmail, ContactMobile) values('004', 'Reena', 'Reena@code-sample.com','9015009999')
Insert into [USER](UID, ContactName, ContactEmail, ContactMobile) values('005', 'Aradhya', 'Aradhya@code-sample.com','9015519000')


--INSERT TABLE MOBILE

Insert into Mobile (UID, Account, Mobile, AlertToMobile, AlertToEmail) values('001', 'AC001','9015519999','Yes', 'No')
Insert into Mobile (UID, Account, Mobile, AlertToMobile, AlertToEmail) values('002', 'AC002','9015500999','Yes', 'No')
Insert into Mobile (UID, Account, Mobile, AlertToMobile, AlertToEmail) values('003', 'AC003','9010019999','No', 'No')
Insert into Mobile (UID, Account, Mobile, AlertToMobile, AlertToEmail) values('004', 'AC004','9015009999','Yes', 'Yes')
Insert into Mobile (UID, Account, Mobile, AlertToMobile, AlertToEmail) values('005', 'AC005','9015519000','No', 'Yes')


--QUERY FOR USE NOT EXISTS WITH HELP OF TABLE USER AND MOBILE

SELECT * 
FROM [USER] AS USR
    WHERE  NOT EXISTS (
       SELECT *
       FROM MOBILE MOB
       WHERE USR.UID = MOB.UID
       AND   USR.CONTACTMOBILE = MOB.MOBILE);


--QUERY EXECUTION PLAN











--RESULT WINDOW












--QUERY FOR USE EXISTS WITH HELP OF TABLE USER AND MOBILE

SELECT * 
FROM [USER] AS USR
    WHERE  EXISTS (
       SELECT *
       FROM MOBILE MOB
       WHERE USR.UID = MOB.UID
       AND   USR.CONTACTMOBILE = MOB.MOBILE);


--QUERY EXECUTION PLAN











--RESULT WINDOW




ANIL SINGH

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2

www.code-sample.com/. Powered by Blogger.
^