Predicates in SQL Server 2008
Any operator that is used within the select clause condition in where clause is a predicate. SQL provides several of these predicates to simplify the conditions used in where clause.
In predicate can be used to check the specific value is present in the list of values.
a. Consider an example where we have to find the student from “Harvard” & “Illinois” university then the query using “IN” predicate is as follows.
SELECT * FROM dbo.STUDENT WHERE UNIVERSITY IN (‘Harvard’ , ‘Illinois’);
b. Find the student who joined the University on the specific date (9-MAY-2008).
SELECT * FROM dbo.STUDENT WHERE JOININGDATE IN (‘9-MAY-2008’);
- IS NULL
This predicate is used to search the “NULL” values present in the column.
e.g. Find the student whose Date of Birth is not entered in the System.
SELECT * FROM STUDENT WHERE DATEOFBIRTH IS NULL.
Like predicate is used to check the similar values present in the given column. Like predicate is used with two characters ‘%'(percent) and ‘_'(underscore). ‘%’ is used to get all the combinations for the search string & ‘_’ is used to get the exact result.
Following examples explains the details about the ‘LIKE’ predicate.
a. Get all student whose names start with ‘A’ character.
SELECT * FROM STUDENT WHERE NAME LIKE ‘A%’
Here result will be all names starting with ‘A’ letter.
b. Get all student whose names contains ‘A’ character.
SELECT * FROM STUDENT WHERE NAME LIKE ‘%A%’
c. Get all student whose last name contains ‘A’ as second character. e.g. Last Name “JARED”.
SELECT * FROM STUDENT WHERE LASTNAME LIKE ‘_A%’
d. Get all the student having “A” character twice in their name.
SELECT * FROM STUDENT WHERE NAME LIKE ‘%A%A%’
- BETWEEN & AND
This predicate is used to verify where or not the given value is within a given range.
a. For example we need to find the student having marks in subject between 60 & 80.
SELECT * FROM STUDENT WHERE SUBJECTMARKS BETWEEN 60 AND 80.
b. Get the student count who joined the university on ‘9-MAY-2008′ and ’22-MAY-2015’.
SELECT * FROM STUDENT WHERE JOININGDATE BETWEEN ‘9-MAY-2008′ AND ’22-MAY-2015’.