Predicates in SQL Server 2012

This article demonstrates use of predicates with SQL queries in a SQL Server database. A predicate defines a logical condition being applied to rows in a table. The common logical conditions with two values (true, false) are extended in the SQL language by a third value (unknown). SQL Predicates are found on the tail end of clauses, functions, and SQL expressions in existing query statements. So let's take a look at a practical example of predicates in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 

The Transact-SQL language supports the following predicates:

1. In Operator
2. Exists function
3. Between Operator
4. Like Operator
5. All and any Operator

Creating Table in SQL Server Database

Now create a table named UserDetail with the columns User_ID, UserName, CompanyName and Salary. Set the identity property=true for ID. Now insert some values in this table. The table looks like this:

Now apply predicates on the preceding table named UserDetail.

IN Operators

An IN operator allows the specification of two or more expressions to be used for a query search. The result of the condition is true if the value of the corresponding column equals one of the expressions specified by the IN predicate.

Example

-- IN Operator

Select User_ID, FirstName, LastName, Salary from UserDetail where Salary in(5000,20000);

Output

Exist Function

The exists function and subquery as an argument and returns true if the subquery returns one or more rows, and returns false if it returns zero rows.

The EXISTS operator operates on a subquery and returns a Boolean value either TRUE or FALSE.

• TRUE if the subquery returns at least one row
• FALSE if no rows are returned by the subquery  

Example

This example shows the Exists function and Subquery:

-- Exists Operator

SELECT User_ID, FirstName, LastName, Salary from UserDetail

WHERE  EXISTS

(SELECT * FROM UserDetail WHERE LastName ='kumar' and FirstName='sapan');

The subquery of the EXISTS function almost always depends on a variable from

BETWEEN Operator
 

The BETWEEN operator specifies a range , which determines the lower and upper bounds of qualifying values. This operator using with and operator. The selected data is a range of greater then or equal to 5000 and less then or equal to 20000. The Between operator can be used  with numeric, text and date data types. 

Example

-- BETWEEN Operator

Select User_ID, FirstName, LastName, Salary from UserDetail where Salary BETWEEN 5000 and 20000;

Output

LIKE Operator

LIKE is an operator that compares column values with a specified pattern. During pattern matching, regular characters must exactly match the characters specified in the character string. The data type of the column can be any character or date data type. There are certain characters within the pattern, called wildcard characters.  I have used four types of wildcards; they are:

1. Percent sign (%): It is used to represent or search any string of zero or more characters.
2. Underscore (_): It is used to represent or search a single character.
3. Bracket ([]): It is used to represent or search any single character within the specified range.
4. Caret (^): It is used to represent or search any single character not within the specified range.

Example

-- Like Operator

Select User_ID, FirstName, LastName, Salary from UserDetail where FirstName LIKE '%h%';

 

Output

ANY and ALL Operator

The operators ANY and ALL are always used in combination with comparison operators.

The general syntax of both operator is:

column operator[ ANY | ALL ] query

Where Operator stands for a comparison operator.

ANY Operator

The any operator evaluates to true if the result of an inner query contains at least one row that satisfies the comparison.

Example

-- ANY Operator

Select User_ID, FirstName, LastName, Salary from UserDetail where Salary >
any(select Salary from UserDetail);

Output

ALL Operator

The ALL Operator evaluates to true if the evaluation of the table column in an inner query returns all values of the column.

Example

-- All Operator

Select User_ID, FirstName, LastName, Salary from UserDetail where Salary >=
all(select Salary from UserDetail);

Output