T-SQL Operators

Arithmetic Operators

Used to perform mathematical calculations on numeric values.

  • + (Addition)
  • - (Subtraction)
  • * (Multiplication)
  • / (Division)
  • % (Modulo - returns the remainder of a division)

Example:

SELECT 10 + 5 AS Result; -- Result: 15
SELECT 20 % 3 AS Remainder; -- Remainder: 2

Comparison Operators

Used to compare two expressions. They return TRUE, FALSE, or UNKNOWN.

  • = (Equal to)
  • > (Greater than)
  • < (Less than)
  • >= (Greater than or equal to)
  • <= (Less than or equal to)
  • <> or != (Not equal to)
  • IS NULL (Checks if a value is NULL)
  • IS NOT NULL (Checks if a value is not NULL)

Example:

SELECT ProductName FROM Products WHERE Price > 50;
SELECT * FROM Orders WHERE ShippedDate IS NULL;

Logical Operators

Used to combine conditional statements and filter data based on multiple criteria.

  • AND (Returns TRUE if both conditions are TRUE)
  • OR (Returns TRUE if at least one condition is TRUE)
  • NOT (Reverses the result of a condition)
  • EXISTS (Checks for the existence of rows in a subquery)
  • IN (Allows specifying multiple values in a WHERE clause)
  • BETWEEN (Checks if a value is within a range)
  • LIKE (Performs pattern matching)

Example:

SELECT * FROM Customers WHERE Country = 'USA' AND City = 'New York';
SELECT ProductName FROM Products WHERE Category IN ('Electronics', 'Appliances');
SELECT * FROM Employees WHERE EmployeeName LIKE 'J%';

String Operators

Used for concatenating strings.

  • + (String concatenation - T-SQL specific)
  • CONCAT() (Function for string concatenation, handles NULLs more gracefully)

Example:

SELECT FirstName + ' ' + LastName AS FullName FROM Employees;
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;

Bitwise Operators

Operate on individual bits of integer data.

  • & (Bitwise AND)
  • | (Bitwise OR)
  • ^ (Bitwise XOR)
  • ~ (Bitwise NOT)
  • << (Left shift)
  • >> (Right shift)

Example:

SELECT 10 & 0110 AS Result; -- Result: 2 (Binary 1010 AND 0110 = 0010)

Assignment Operators

Used to assign a value to a variable.

  • = (Assigns the value on the right to the variable on the left)

Example:

DECLARE @MyVariable INT;
SET @MyVariable = 100;
SELECT @MyVariable AS AssignedValue; -- AssignedValue: 100