T-SQL Operators

Operators are special symbols or keywords that specify the type of computation or operation to be performed. T-SQL supports a wide range of operators for various purposes, including arithmetic, comparison, logical operations, and more.

Arithmetic Operators

+ Addition: Adds two expressions.
```
SELECT 10 + 5; -- Result: 15
```
- Subtraction: Subtracts the second expression from the first.
```
SELECT 10 - 5; -- Result: 5
```
* Multiplication: Multiplies two expressions.
```
SELECT 10 * 5; -- Result: 50
```
/ Division: Divides the first expression by the second.
```
SELECT 10 / 5; -- Result: 2
```
% Modulo: Returns the remainder of an integer division.
```
SELECT 10 % 3; -- Result: 1
```

Comparison Operators

= Equal to: Checks if two expressions are equal.
```
SELECT * FROM Products WHERE Price = 19.99;
```
> Greater than: Checks if the first expression is greater than the second.
```
SELECT * FROM Orders WHERE OrderDate > '2023-01-01';
```
< Less than: Checks if the first expression is less than the second.
```
SELECT * FROM Employees WHERE Salary < 50000;
```
>= Greater than or equal to: Checks if the first expression is greater than or equal to the second.
```
SELECT * FROM Inventory WHERE Quantity >= 100;
```
<= Less than or equal to: Checks if the first expression is less than or equal to the second.
```
SELECT * FROM Students WHERE Grade <= 70;
```
<> Not equal to: Checks if two expressions are not equal.
```
SELECT * FROM Customers WHERE Country <> 'USA';
```
!= Not equal to: Alternative syntax for not equal.
```
SELECT * FROM Customers WHERE Country != 'USA';
```

LIKE LIKE: Pattern matching.

SELECT * FROM Employees WHERE LastName LIKE 'S%'; -- Finds employees whose last name starts with S

IN IN: Checks if a value is in a list of values.

SELECT * FROM Products WHERE CategoryID IN (1, 3, 5);

BETWEEN BETWEEN: Checks if a value is within a range.

SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

IS NULL IS NULL: Checks if a value is NULL.

SELECT * FROM Customers WHERE Email IS NULL;

IS NOT NULL IS NOT NULL: Checks if a value is not NULL.
```
SELECT * FROM Products WHERE Description IS NOT NULL;
```

Logical Operators

AND: Combines two conditions; both must be true.

SELECT * FROM Products WHERE Price > 50 AND Category = 'Electronics';

OR: Combines two conditions; at least one must be true.

SELECT * FROM Employees WHERE Department = 'Sales' OR Department = 'Marketing';

NOT: Reverses the logical state of its operand.

SELECT * FROM Customers WHERE NOT Country = 'Canada';

ALL: Compares a value to all values in a subquery or list.

SELECT * FROM Products WHERE Price > ALL (SELECT Price FROM CompetitorProducts);

ANY: Compares a value to any value in a subquery or list.

SELECT * FROM Orders WHERE OrderID = ANY (SELECT OrderID FROM ShippedOrders);

EXISTS: Checks for the existence of rows in a subquery.

SELECT * FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);

UNIQUE: Checks if all rows in a subquery are unique.

SELECT * FROM Orders WHERE UNIQUE (SELECT OrderID FROM OrderDetails WHERE OrderDetails.OrderID = Orders.OrderID);

Bitwise Operators

& Bitwise AND: Performs a bitwise AND operation.
| Bitwise OR: Performs a bitwise OR operation.
^ Bitwise XOR: Performs a bitwise XOR operation.
~ Bitwise NOT: Performs a bitwise NOT operation.
<< Bitwise LEFT SHIFT: Shifts bits to the left.
>> Bitwise RIGHT SHIFT: Shifts bits to the right.

Assignment Operators

= Assignment: Assigns a value to a variable.
```
DECLARE @Counter INT = 0;
```

String Concatenation Operators

+ Concatenation: Joins two or more strings.

SELECT 'SQL' + ' Server'; -- Result: 'SQL Server'

|| Concatenation (ANSI SQL Standard): Joins two or more strings. Note: Behavior may vary depending on SQL Server settings.
```
SELECT 'T' || 'SQL'; -- Result: 'TSQL' (if configured)
```

Understanding and correctly using these operators is fundamental to writing effective T-SQL queries and manipulating data in SQL Server.