SQL operators are used to perform operations on data stored in databases. They can be categorized into several types, such as arithmetic, comparison, logical, and others. Understanding these operators is fundamental for querying and manipulating data in SQL Server.
1. Arithmetic Operators
Arithmetic operators perform mathematical operations on numeric data.
Operators:
+ (Addition)
– (Subtraction)
* (Multiplication)
/ (Division)
% (Modulo)
Example:
Let’s consider a table Products with the following data:
SELECT ProductName, Price, Quantity,
Price * Quantity AS TotalValue
FROM Products;
2. Comparison Operators
Comparison operators are used to compare two values. They return a boolean result (true or false).
Operators:
= (Equal)
<> or != (Not Equal)
> (Greater Than)
< (Less Than) >= (Greater Than or Equal To)
<= (Less Than or Equal To)
Example:
Query to find products with a price greater than 1.00:
SELECT ProductName, Price
FROM Products
WHERE Price > 1.00;
3. Logical Operators
Logical operators are used to combine multiple conditions in a WHERE clause.
Operators:
AND
OR
NOT
Example:
Query to find products with a price less than 2.00 and quantity greater than 100:
SELECT ProductName, Price, Quantity
FROM Products
WHERE Price < 2.00 AND Quantity > 100;
4. Bitwise Operators
Bitwise operators perform bit-level operations.
Operators:
& (Bitwise AND)
| (Bitwise OR)
^ (Bitwise XOR)
~ (Bitwise NOT)
Example:
Using bitwise AND to filter products (assuming ProductID is used in a bitwise operation):
SELECT ProductID, ProductName
FROM Products
WHERE ProductID & 1 = 1;
5. String Operators
String operators are used to perform operations on string data.
Operators:
+ (Concatenation in some SQL dialects, but in MSSQL, use CONCAT function)
Example:
Concatenating product name and price:
SELECT ProductName + ‘ – $’ + CAST(Price AS VARCHAR) AS ProductInfo
FROM Products;SELECT ProductName + ‘ – $’ + CAST(Price AS VARCHAR) AS ProductInfo
FROM Products;
6. Set Operators
Set operators combine the results of two queries.
Operators:
UNION
UNION ALL
INTERSECT
EXCEPT
Example:
Assume we have another table DiscountedProducts:
Query to find all unique products:
SELECT ProductName
FROM Products
UNION
SELECT ProductName
FROM DiscountedProducts;
Other Operators
There are additional operators such as:
7 . IN Operator
Checks if a value is within a set of values.
Example:
SELECT ProductName, Price
FROM Products
WHERE ProductName IN (‘Pen’, ‘Eraser’);
8. BETWEEN Operator
Selects values within a given range.
Example:
SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 1.00 AND 2.00;
9. LIKE Operator
Searches for a specified pattern in a column.
Example:
SELECT ProductName
FROM Products
WHERE ProductName LIKE ‘P%’;
Practice using these operators with different queries to become proficient in SQL.