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.
- Query Syntax Exists : WHERE EXISTS (--subquery);
- 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 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..
|
