Exists structure in SQL Server

Exists Structure

The exists structure is a tsql function, which returns a Boolean variable for the subquery in parentheses. If the subquery returns any row, the structure returns true; otherwise, it returns false. This structure is especially suitable for querying whether some records exist in the table. It is also very suitable for deciding whether to run update or insert statements in the table (in this article, I will introduce it to you later ).

Listing a creates a table and inserts some data, so that you can use this table to see how the exists structure works. The script in listing B uses the exists structure to check each row in the saleshistory table and obtain a variable based on the existing row. This script returns 1 (true), which means that the saleshistory table contains the row you want to query.

In this case, once a record is found in the saleshistory table, the exists structure will not continue searching and 1 will be returned immediately. The additional benefit of using the exists structure is that once it finds a record that meets the conditions, it will return immediately.

Listing c is similar to the preceding script, but its return value is 0, because the exists condition does not meet this condition.

Because of the working principle of the exists structure, the following statements implement the same functions.

Select * From saleshistory

Where exists (select null)

Select * From saleshistory

Where 1 = 1

Select * From saleshistory

Where exists (select 1)

In the script, you may be confused about the following statements:

Select * From saleshistory

Where exists (select null)

Because null is a value in the database (it is an unknown value), the exists structure returns true in the case of null values.

Developers seldom use exists to perform operations on subqueries. The following query returns the same result set, although they have different structures. The first query uses the related subquery of exists, and the second query compares the saleprice field with the total average value of the field in the saleshistory table.

Select * From saleshistory sh

Where exists

(

Select AVG (saleprice)

　　

From saleshistory Shi

　　

Having AVG (saleprice)> Sh. saleprice

)

Select * From saleshistory sh

Where saleprice <

(

Select AVG (saleprice)

　　

From saleshistory Shi

)

You can use the exists operation to determine whether to use the update or insert statement for a single record, just as in the following example:

If exists

(

Select * From saleshistory

Where saledate = '2014/1/123' and

Product = 'computer 'and

Saleprice = 1000

)

Update top (1) saleshistory

Set saleprice = 1100

Where saledate = '2014/1/123' and

Product = 'computer 'and

Saleprice = 1000

Else

Insert into saleshistory

(Product, saledate, saleprice)

Values

('Computer ', '2014/1/123', 1/1)