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:
- In Operator
- Exists function
- Between Operator
- Like Operator
- 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.
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.
-- IN Operator
Select User_ID, FirstName, LastName, Salary from UserDetail where Salary in(5000,20000);
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
This example shows the Exists function and Subquery:
-- Exists Operator
SELECT User_ID, FirstName, LastName, Salary from UserDetail
(SELECT * FROM UserDetail WHERE LastName ='kumar' and FirstName='sapan');
The subquery of the EXISTS function almost always depends on a variable from
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.
-- BETWEEN Operator
Select User_ID, FirstName, LastName, Salary from UserDetail where Salary BETWEEN 5000 and 20000;
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:
- Percent sign (%): It is used to represent or search any string of zero or more characters.
- Underscore (_): It is used to represent or search a single character.
- Bracket (): It is used to represent or search any single character within the specified range.
- Caret (^): It is used to represent or search any single character not within the specified range.
-- Like Operator
Select User_ID, FirstName, LastName, Salary from UserDetail where FirstName LIKE '%h%';
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.
-- ANY Operator
Select User_ID, FirstName, LastName, Salary from UserDetail where Salary >
any(select Salary from UserDetail);
The ALL Operator evaluates to true if the evaluation of the table column in an inner query returns all values of the column.
-- All Operator
Select User_ID, FirstName, LastName, Salary from UserDetail where Salary >=
all(select Salary from UserDetail);