Introduction of SQL Logical Operators With Practical Example
Summary: In this tutorial, you will learn introduction of SQL Logical Operators (Transact-SQL) With Practical Example, and how to use sql logical operators to test for the truth of a condition.
What is SQL Logical Operators?
- SQL Logical operators test for the truth of some conditions. Logical operators, such as comparison operators, thus, return result set a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.
- A logical operator permits you to test for the truth of a condition. The same as a comparison operator, a logical operator returns a value of true, false, or unknown.
- Basically, there are three types of Logical Operators namely, AND, OR, and NOT. These operators will compare two conditions at a time to determine whether a row can be selected for the output.
- Once retrieving data using a SELECT statement, you’ll be able to use logical operators in the WHERE clause, which allows you to combine more than one condition.
The Given Below Table Clarifies The Difference And Their Use.
| Operators | Description |
|---|---|
| ALL | The ALL operator will return true when the value matches all values in a single column set of values. Therefore, it’s like AND operator it will compare the value against all values in the column. |
| AND | The AND operator is used to compare data with more than one condition. If all the conditions return TRUE then only it’ll show records. |
| ANY | Any operator in SQL Server will return true when the value matches any value in a single column set of values. Therefore, it’s like an OR operator and it will compare the value against any value in the column. |
| BETWEEN | The BETWEEN operator is used to get the values within a range. |
| EXISTS | The EXISTS operator is used to show the result if the subquery returns data. |
| IN | The IN operator in SQL is used to search for a specified value that matches any value in the set of multiple values. |
| LIKE | The LIKE operator in SQL Server is used to search for character strings with the specified pattern using wildcards in a column. |
| NOT | The NOT operator reverses the value of any other Boolean operator. it will show data for the opposite of the conditions that we mentioned in the SQL statement. |
| OR | The OR operator in SQL Server is used to compare data with more than one condition. Thus, if either of the condition is TRUE it will return the data. |
| SOME | The SOME operator returns TRUE if some of a set of comparisons are TRUE means can be used to compare the value with a single column set of values and returned by the subquery. |
SQL Logical Operators Practical Example
ALL
Let’s compare a scalar value with a single-column set of values.
The given below, the following query returns all if all the StateCode greater than 107. If at least one state code is less than 107 then it doesn’t, return any records. Here States Tamil Nadu and Odisha have state codes greater than 107, so the condition fails and the result is nothing.
|
1 2 3 4 5 |
SELECT * FROM Tbl_Unemployment WHERE 107 > ALL ( SELECT StateCode FROM Tbl_Unemployment ) |
Nothing
AND
Here, the given below example performs a logical AND operation. Therefore, in the demonstration expression evaluates to TRUE if all conditions are TRUE.
|
1 2 |
SELECT * FROM Tbl_Unemployment WHERE (StateCode > 101 AND StateCode < 107) |
ANY and SOME
Let’s compare a scalar value with a single-column set of values.
Both SOME or ANY operators return the TRUE when the comparison specified is TRUE for ANY pair, otherwise, it will return FALSE.
In the given table there are some states in which StateCode are less than 107, so it will return all the records.
ANY
|
1 2 3 4 5 |
SELECT * FROM Tbl_Unemployment WHERE 107 > ANY ( SELECT StateCode FROM Tbl_Unemployment ) |
SOME
|
1 2 3 4 5 |
SELECT * FROM Tbl_Unemployment WHERE 107 > SOME ( SELECT StateCode FROM Tbl_Unemployment ) |
BETWEEN
Here, in this demonstration we specifies a range to test.
|
1 2 |
SELECT * FROM Tbl_Unemployment WHERE StateCode BETWEEN 100 AND 120 |
EXISTS
The given below SQL query we specifies a subquery to test for the existence of rows.
|
1 2 3 4 5 |
SELECT * FROM Tbl_Unemployment WHERE EXISTS ( SELECT * FROM Tbl_Unemployment WHERE StateCode=107 ) |
Therefore, it returns data when a specified record exist in the “Tbl_Unemployment” table which is given in sub query of where condition
IN
Here, with IN operator we determines the whether a given value matches with any value in a subquery or a list.
|
1 2 |
SELECT * FROM Tbl_Unemployment WHERE StateCode IN (101,105,107,109,110) |
LIKE
Identified whether a specific character string matches a specified pattern. Therefore, a pattern can include wildcard characters and regular characters. throughout the pattern matching, here, regular characters must be exactly matched with the characters which are specified in the character string. However, the wildcard characters can be matched with a character string. Therefore, when using the wildcard characters makes the LIKE operator. it more flexible than using the = and != string comparison operators. Thus, if any one of the arguments isn’t of character string data type, the SQL Server 2005 Database Engine converts them to character string data type, if it is possible.
|
1 2 |
SELECT * FROM Tbl_Unemployment WHERE StateName LIKE 'T%' |
NOT
This operator is used to find rows that do not match a value, we can use the NOT operator.
|
1 2 |
SELECT * FROM Tbl_Unemployment WHERE StateCode NOT IN (104,107,111,102) |
OR
Here, this operator is used to perform a logical OR operation. Thus, the expression evaluates to TRUE if at least one condition is TRUE.
|
1 2 |
SELECT * FROM Tbl_Unemployment WHERE StateName LIKE 'K%' OR StateCode < 103 |
Conclusion:
In this article, you have learned How to use SQL Logical Operators (Transact-SQL) With Practical Example. I hope you will enjoy it!
About Author
