NEWS

Aug 7 2017

SQL Server: EXISTS Condition #techonthenet, #tech #on #the #net, #totn, #sql #server, #transact-sql, #t-sql, #tsql, #exists #condition, #syntax, #parameters, #arguments, #example, #tutorial


#

SQL Server: EXISTS Condition

This SQL Server tutorial explains how to use the EXISTS condition in SQL Server (Transact-SQL) with syntax and examples.

The SQL Server (Transact-SQL) EXISTS condition is used in combination with a subquery and is considered to be met if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the EXISTS condition in SQL Server (Transact-SQL) is:

Parameters or Arguments

subquery The subquery is a SELECT statement. If the subquery returns at least one record in its result set, the EXISTS clause will evaluate to true and the EXISTS condition will be met. If the subquery does not return any records, the EXISTS clause will evaluate to false and the EXISTS condition will not be met.

Note

  • SQL statements that use the EXISTS condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query’s table. There are more efficient ways to write most queries, that do not use the EXISTS condition.

Example – With SELECT Statement

Let’s look at a simple example.

The following is a SELECT statement that uses the EXISTS condition:

This SQL Server EXISTS condition example will return all records from the employees table where there is at least one record in the contacts table with a matching last_name and first_name .

Example – With SELECT Statement using NOT EXISTS

The SQL Server EXISTS condition can also be combined with the NOT operator .

This SQL Server EXISTS example will return all records from the employees table where there are no records in the contacts table for the matching last_name and first_name .

Example – With INSERT Statement

The following is an example of an INSERT statement that uses the EXISTS condition:

Example – With UPDATE Statement

The following is an example of an UPDATE statement that uses the EXISTS condition:

Example – With DELETE Statement

The following is an example of a DELETE statement that uses the EXISTS condition:


Written by admin


Leave a Reply

Your email address will not be published. Required fields are marked *