Transact-SQL Operators

Operators are symbols or keywords that specify the type of calculation or operation to be performed in a Transact-SQL statement. Transact-SQL supports a wide range of operators for various purposes.

Arithmetic Operators

These operators perform mathematical calculations on numeric operands.

Operator Description Example
+ Addition SELECT 10 + 5;
- Subtraction SELECT 10 - 5;
* Multiplication SELECT 10 * 5;
/ Division SELECT 10 / 5;
% Modulo (Returns the remainder of a division) SELECT 10 % 3;
+ (String Concatenation) Concatenates two strings SELECT 'Hello' + ' World';

Bitwise Operators

These operators manipulate the individual bits of integer operands.

Operator Description Example
& Bitwise AND SELECT 5 & 3;
| Bitwise OR SELECT 5 | 3;
^ Bitwise XOR SELECT 5 ^ 3;
~ Bitwise NOT SELECT ~5;
<< Left shift SELECT 5 << 1;
>> Right shift SELECT 5 >> 1;

Comparison Operators

These operators compare two expressions and return a Boolean value (TRUE, FALSE, or UNKNOWN).

Operator Description Example
= Equal to SELECT * FROM Employees WHERE Department = 'Sales';
> Greater than SELECT * FROM Products WHERE Price > 100.00;
< Less than SELECT * FROM Orders WHERE OrderDate < '2023-01-01';
>= Greater than or equal to SELECT * FROM Customers WHERE Age >= 18;
<= Less than or equal to SELECT * FROM Inventory WHERE Quantity <= 10;
<> or != Not equal to SELECT * FROM Status WHERE Status_Code <> 0;
IS NULL Checks if a value is NULL SELECT * FROM Users WHERE Email IS NULL;
IS NOT NULL Checks if a value is not NULL SELECT * FROM Employees WHERE HireDate IS NOT NULL;
BETWEEN Checks if a value is within a range (inclusive) SELECT * FROM Sales WHERE Amount BETWEEN 100 AND 500;
LIKE Performs pattern matching SELECT * FROM Products WHERE ProductName LIKE 'A%';
IN Checks if a value is in a list of values SELECT * FROM Employees WHERE EmployeeID IN (101, 105, 203);

Logical Operators

These operators combine Boolean expressions.

Operator Description Example
AND Returns TRUE if both conditions are TRUE SELECT * FROM Employees WHERE Department = 'Sales' AND Salary > 50000;
OR Returns TRUE if at least one condition is TRUE SELECT * FROM Orders WHERE Status = 'Shipped' OR Status = 'Delivered';
NOT Reverses the Boolean value of a condition SELECT * FROM Products WHERE NOT IsActive;

Set Operators

These operators combine the result sets of two or more SELECT statements.

Operator Description Example
UNION Combines result sets and removes duplicates SELECT EmployeeID FROM Employees UNION SELECT CustomerID FROM Customers;
UNION ALL Combines result sets and includes all rows, including duplicates SELECT EmployeeID FROM Employees UNION ALL SELECT CustomerID FROM Customers;
INTERSECT Returns only rows that exist in both result sets SELECT ColumnA FROM Table1 INTERSECT SELECT ColumnA FROM Table2;
EXCEPT Returns rows from the first result set that are not in the second result set SELECT ColumnA FROM Table1 EXCEPT SELECT ColumnA FROM Table2;

Special Operators

Other important operators available in Transact-SQL.

Operator Description Example
ALL Used with comparison operators to compare a value to all values in a list or subquery. SELECT MAX(Price) FROM Products WHERE Price > ALL (SELECT Price FROM CompetitorPrices);
ANY Used with comparison operators to compare a value to any value in a list or subquery. SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 10);
DISTINCT Returns only unique values. SELECT DISTINCT City FROM Customers;
EXISTS Checks for the existence of rows in a subquery. SELECT * FROM Departments WHERE EXISTS (SELECT 1 FROM Employees WHERE Employees.DepartmentID = Departments.DepartmentID AND Employees.Salary > 100000);
NOT EXISTS Checks for the non-existence of rows in a subquery. SELECT * FROM Customers WHERE NOT EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);

Operator Precedence

Operators are evaluated in a specific order, known as operator precedence. When multiple operators are present in an expression, the operators with higher precedence are evaluated first. Parentheses can be used to control the order of evaluation.

Example of precedence:

SELECT 10 + 5 * 2; -- Multiplication is evaluated before addition -- Result: 20 SELECT (10 + 5) * 2; -- Parentheses force addition to be evaluated first -- Result: 30

For a detailed table of operator precedence in Transact-SQL, please refer to the official Microsoft SQL Server documentation.