Wednesday, June 2, 2010

Understanding the EXISTS Keyword in SQL Server 2008

What if when you need to check some value existence in your DB?
You can   use the EXISTS clause with a subquery. The EXISTS Keyword, always returns a TRUE or FALSE value.

 The EXISTS clause checks for the existence of rows according to the condition specified in the inner query and passes the existence status to the outer query. The subquery returns a true value if the result of the subquery contains any row. The query introduced by with the EXISTS keyword differs from other queries. The EXISTS keyword is not preceded by any column name, Constant, or other expression, and it contains an asterisk (*) in the SELECT list of the inner query.

The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS (subquery);

Query of EXISTS Keyword.

create table Emp_tab1
(
[Emp name] varchar(30),
[Emp D-O-J] varchar(30),
[Emp Salary] bigint,
)
Insert into Emp_tab1 values ( 'Raj kumar' ,'15-12-2005',32000)
Insert into Emp_tab1 values ( 'Rohit singh' ,'15-12-2005',65000)
Insert into Emp_tab1 values ( 'Rikesh jha' ,'10-12-1999',5000)
Insert into Emp_tab1 values ( 'Monika panday' ,'02-05-2009',70000)
Insert into Emp_tab1 values ( 'Raj kumar' ,'15-12-2005',32000)
Insert into Emp_tab1 values ( 'Rikesh jha' ,'10-12-1999',5000)
Insert into Emp_tab1 values ( 'Vikas singh' ,'01-05-2008',85000)

create table Emp_tab2
(
[Emp name] varchar(30),
[Emp D-O-J] varchar(30),

)
Insert into Emp_tab2 values ( 'Raj kumar' ,'15-12-2005')
Insert into Emp_tab2 values ( 'Rohit singh' ,'15-12-2005')
Insert into Emp_tab2 values ( 'Rikesh jha' ,'10-12-1999')
Insert into Emp_tab2 values ( 'Monika panday' ,'02-05-2009')
Insert into Emp_tab2 values ( 'Raj kumar' ,'15-12-2005')
Insert into Emp_tab2 values ( 'Rikesh jha' ,'10-12-1999') 

command in EXISTS query. 

SELECT * FROM dbo.Emp_tab1 WHERE EXISTS(SELECT [Emp name] FROM dbo.Emp_tab2)

The result is displayed below with EXISTS Keyword. 

1 comment:

Recent Posts