Other in SQL Server and in SQL Server are just a few of the common comparison operators. Here's what you need to know about other operators in SQL Server.
SQL Server is knowledge that every programmer needs to know. SQL Server is special software developed by Microsoft for database management systems. It is primarily used to create, maintain, and deploy relational database management systems.
SQL Server is currently used very commonly, especially when data security in the information technology industry becomes increasingly important. On the other hand, it is also optimized to run databases as large as tera bytes, which can serve thousands of users at the same time.
Basically, learning SQL Server is not difficult. You need to start from the most basic knowledge. Quantrimang.com is a source for learning SQL Server worth trying for you. In this lesson, let's learn about comparison operators in SQL Server!
In SQL, you have up to 6 comparison operators available. They help programmers run queries to implement various operations. For example, you would use the WHERE statement with conditional operators to achieve that in SQL. In this article, let's learn in detail about comparison operators when using SQL!
This article will introduce comparison operators used to check balance along with more advanced operators in SQL Server. Readers can refer to the article Operators in SQL.
What are comparison operators in SQL Server?
Comparison operators in SQL Server are used to compare two values. So you'll see them when you compare one expression with another. The result of the comparison can be True, False or Unknown (an operator with one or two NULL expressions returns UNKNOWN).
Note, you can use comparison operators on all expressions, except expressions of type text, image, or ntext.
The comparison operator is used in the WHERE clause to determine which record to select. This is a list of comparison operators you can use in SQL Server (T-SQL).
Operator | Describe |
= | Equal |
Not equal | |
!= | Not equal |
> | Bigger |
>= | Greater than or equal to |
Smaller | |
Less than or equal | |
!> | Not bigger |
! | Not smaller |
IN() | Matches the value in the list |
NOT | Does not meet conditions |
BETWEEN | Located in a range (including start and end values) |
IS NULL | NULL value |
IS NOT NULL | Value is not NULL |
LIKE | Match pattern with % and _ |
EXISTS | The condition is met if the internal query (subquery) returns at least 1 row |
Boolean data type
The result of the comparison operator has a Boolean data type. It has 3 values: TRUE, FALSE and UNKNOWN. Expressions that return a Boolean data type are called Boolean expressions.
Unlike other data types in SQL Server, a Boolean data type cannot be considered the data type of a column or variable in a table and cannot be returned as a result file.
When SET ANSI_NULLS is ON, the operator with one or two NULL expressions returns UNKNOW. When SET ANSI_NULLS is OFF, the same rules apply, except for the equal and not equal operators. When SET ANSI_NULLS is OFF, these operators will be NULL, equivalent to any other NULL, and return only TRUE or FALSE.
Expressions with the Boolean data type are used in WHERE clauses to filter rows that apply search conditions and flow control language commands, such as IF and WHILE, for example:
-- Uses AdventureWorks
DECLARE @MyProduct INT;
SET @MyProduct = 750;
IF (@MyProduct 0)
SELECT ProductID, Name, ProductNumber
FROM Production.Product
WHERE ProductID = @MyProduct;
There are many more comparison operators in SQL Server and Transact-SQL. Below is how to use some common operators.
Example – Peer operator
SELECT *
FROM nhanvien
WHERE ten = ‘Jane’;
In this example, the SELECT statement will return rows from the employee table with employees named Jane.
Example – Non-peer operator
In SQL Server, you can use or != to check for non-parity in a query.
SELECT *
FROM nhanvien
WHERE ten ‘Jane’;
The SELECT command above will return rows in the employee table whose name is not Jane. The above command can be written differently as follows. Both queries return the same set of results.
SELECT *
FROM nhanvien
WHERE ten != ‘Jane’;
Example – Greater than operator
SELECT *
FROM nhanvien
WHERE nhanvien_id > 3000;
The return result of the above SELECT command is the rows in the employee table where the employee ID is greater than 3000, the employee ID equal to 3000 will not be in the results.
Example – Greater than or equal to operator
SELECT *
FROM nhanvien
WHERE nhanvien_id >= 3000;
The above example will return rows with employee ID greater than or equal to 3000 in the employees table.
Example – Less than operator
SELECT*
FROM nhanvien
WHERE nhanv
ien_idTương tự, kết quả của lệnh này là các hàng trong băng nhanvien có ID của nhân viên nhỏ hơn 500, không bao gồm ID 500.
Ví dụ - Toán tử nhỏ hơn hoặc bằng
SELECT *FROM nhanvien
WHERE nhanvien_id
Kết quả của lệnh này là các hàng trong bảng nhanvien có nhanvien_id nhỏ hơn hoặc bằng 500.
Ví dụ - Toán tử nâng cao hay toán tử logic
Các toán tử nâng cao dưới đây sẽ có từng bài hướng dẫn cụ thể cho chúng.
IN ()
NOT
BETWEEN
IS NULL
IS NOT NULL
LIKE
EXISTSNgoài các toán tử so sánh, bạn cũng cần biết những toán tử logic sau trong SQL:
Operator |
Describe |
ALL |
TRUE if all subquery values meet the condition |
AND |
TRUE if all conditions separated by AND are TRUE |
ANY |
TRUE if any subquery value meets the condition |
BETWEEN |
TRUE if the operand is within the comparison range |
EXISTS |
TRUE if the subquery returns one or more records |
|
TRUE if the operand is equal to one of the list of expressions |
LIKE |
TRUE if the operand matches a pattern |
NOT |
Show a record if the condition is NOT TRUE |
OR |
TRUE if any condition separated by OR is TRUE |
SOME |
TRUE if any subquery value meets the condition |
Previous article: FROM clause in SQL Server
Next article: WHERE clause in SQL Server