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

Hey! I'm Anil Singh. I author this blog. I'm Active Blogger, Programmer. I love learning new technologies, programming, blogging and participating the forum discussions more...
My Blogs - http://www.code-sample.com and http://www.code-sample.xyz
My Books - Buy Books Online at Best Prices

You Might Also Like
Post a Comment
www.code-sample.com/. Powered by Blogger.