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', '[email protected]','9015519999')
Insert into [USER](UID, ContactName, ContactEmail, ContactMobile) values('002', 'Sunil', '[email protected]','9015500999')
Insert into [USER](UID, ContactName, ContactEmail, ContactMobile) values('003', 'Sushil', '[email protected]','9010019999')
Insert into [USER](UID, ContactName, ContactEmail, ContactMobile) values('004', 'Reena', '[email protected]','9015009999')
Insert into [USER](UID, ContactName, ContactEmail, ContactMobile) values('005', 'Aradhya', '[email protected]','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 Blogs - https://code-sample.com and https://code-sample.xyz
My Book1 - BEST SELLING ANGULAR BOOK (INCLUDING ALL VERSIONS 2, 4, 5, 6, 7)
My Book2 - ANGULAR 2 INTERVIEW QUESTIONS BOOK - Both Books are Available on WorldWide.

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