T-SQL queries with examples using the most common T-SQL logical operators in order to compare 2 values.
Here is a list with the comparison and logic operators commonly used in T-SQL language with various examples. The examples shown here range from the simplest to the most complex.
In addition, the code for creating the example tables, as well as the result of the queries, are available in each and every section of this article.
Table of Contents
1. Prepare tables for our T-SQL operators examples
The 4 tables used here are as follows, they are deliberately basic to easily illustrate the cases.
- Customers
- Orders
- Products
- Employees
1.1 Create the 4 tables used in the T-SQL queries
Run this code from an SSMS window to add the 4 following tables to your database.
/* T-SQL code by https://expert-only.com */ CREATE TABLE Customers ( CustomerID INT, CustomerName VARCHAR(255), Country VARCHAR(255) ); CREATE TABLE Orders ( OrderID INT, CustomerID INT, OrderDate DATE ); CREATE TABLE Products ( ProductID INT, ProductName VARCHAR(255), Price DECIMAL(10,2), ProductWeight DECIMAL(10,2) ); CREATE TABLE Employees ( EmployeeID INT, EmployeeName VARCHAR(255), Age INT, Department VARCHAR(255) );
1.2 Insert a few lines of sample data into each table
Copy, paste and run these SQL lines to insert a few rows into each of the 4 tables, still using the SSMS software.
/* T-SQL code by https://expert-only.com */ INSERT INTO Customers (CustomerID, CustomerName, Country) VALUES (1, 'John Smith', 'USA'), (2, 'Jane Doe', 'Canada'), (3, 'Bob Johnson', 'USA'); INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (1, 1, '2022-01-01'), (2, 1, '2022-02-01'), (3, 2, '2022-03-01'), (4, 3, '2022-04-01'); INSERT INTO Products (ProductID, ProductName, Price, ProductWeight) VALUES (1, 'Computer', 1000, 20), (2, 'Printer', 500, 10), (3, 'Monitor', 300, 15); INSERT INTO Employees (EmployeeID, EmployeeName, Age, Department) VALUES (1, 'John Smith', 35, 'IT'), (2, 'Jane Doe', 30, 'Sales'), (3, 'Bob Johnson', 40, 'IT');
Once the tables are created and populated with some example data, let’s move on to practice with concrete use cases.
The list of T-SQL comparison operators covered in this tutorial is as follows:
- Equal to: =
- Greater than: >
- Greater than or equal to: >=
- Less than: <
- Less than or equal to: <=
- And: AND
- Or: OR
- Like: LIKE
- Contains: CONTAINS
2. The Equal comparison operator
Of course one of the most used operator to compare values in T-SQL is the equal symbol. The comparison operator “=”, equal in English, is used to compare if two values are equal. It returns true or false.
Simple example
/* T-SQL code by https://expert-only.com */ SELECT * FROM Customers WHERE CustomerID = 1;
The result is a single row with client number 1, John Smith.
| ClientID | Name | Country |
|---|---|---|
| 1 | John Smith | USA |
Complex example
/* T-SQL code by https://expert-only.com */ SELECT COUNT(*) FROM Orders WHERE OrderDate = ( SELECT MAX(OrderDate) FROM Orders );
The query returns 1.
This query returns the number of orders that have the most recent order date. It uses the “=” comparison operator to compare the date of each order with the most recent order date, returned by the subquery:
/* T-SQL code by https://expert-only.com */ SELECT MAX(OrderDate) FROM Orders;
The query returns the latest date, i.e. 2022-04-01.
3. SQL Server comparison operator Different from
The comparison operator “<>” or “!=”, called different from in English, is used to compare if two values are different. So it returns true if values are evaluated different by the system, and false is values are identical.
Simple example
This first simple example shows customers whose country is different from USA.
/* T-SQL code by https://expert-only.com */ SELECT * FROM Customers WHERE Country <> 'USA';
The result is therefore the only client with Canada as a country.
| ClientID | Name | Country |
|---|---|---|
| 2 | Jane Doe | Canada |
Complex example
This query returns customers who are in a country other than the United States and who have placed an order that includes at least one product priced above 500. It uses the “<>” comparison operator to filter out customers who are not in the United States.
It also uses the “AND” operator to combine this filter with the expensive product filter. In addition, a T-SQL join is needed to link the Clients, Orders, and Products tables.
/* T-SQL code by https://expert-only.com */
SELECT *
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
JOIN Products p
ON o.OrderID = p.ProductID
WHERE c.Country <> 'USA'
AND p.Price > 50;
4. T-SQL comparison operators greater than or less than
The comparison operator > or <, that is to say strictly greater than or less than, is used to compare if a value is greater than another.
Simple example
/* T-SQL code by https://expert-only.com */ SELECT * FROM Products WHERE Price > 500;
| ProductID | ProductName | Price | Weight |
|---|---|---|---|
| 1 | Computer | 1000 | 20 |
The result is the only product with a price above 500, the computer.
Complex example
This query returns the names of customers who have placed more than one order. It uses the > comparison operator to filter groups of customers with more than one order, using the HAVING COUNT(*) > 1 clause.
/* T-SQL code by https://expert-only.com */ SELECT c.CustomerName, COUNT(*) as 'Number of orders' FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName HAVING COUNT(*) > 1;
The result is as follows, with John Smith being the customer who placed 2 orders. To write a T-SQL query and use the less than comparison operator, use the same logic, but in reverse.
| Name | Number of orders |
|---|---|
| John Smith | 2 |
To go further on manipulating data using SQL Server code, these examples help you understand the different data types available in SQL.
5. SQL Server greater or equal to and less or equal to operators
The greater than or equal and less than or equal comparison operators, >= or <=, allow to compare if a value is greater than or equal to another, or less than or equal to another, respectively.
Simple example using the greater or equal logical operator
/* T-SQL code by https://expert-only.com */ SELECT * FROM Employees WHERE Age >= 35;
| EmployeeID | EmployeeName | Age | Department |
|---|---|---|---|
| 1 | John Smith | 35 | IT |
| 3 | Bob Johnson | 40 | IT |
Complex example
This query returns the average price of products that have a weight greater than or equal to 15 or a price greater than or equal to 500. It uses the “>=” (greater than or equal to) comparison operator to filter products based on their weight and price, using the clause WHERE Poids >= 15 OR Prix >= 500.
/* T-SQL code by https://expert-only.com */ SELECT AVG(Price) as 'Average price' FROM Products WHERE ProductWeight >= 15 OR Price >= 500;
The result is displayed in one unique column showing the average price, as follows:
| Average price |
|---|
| 800 |
To write a query with the less than or equal to comparison operator, use the same logic.
6. T-SQL logical operator AND
The AND logic operator, allows you to combine multiple filtering conditions in the WHERE clause of a query. Rows that do not satisfy all conditions are excluded from the result.
SQL Server query example using the AND operator
/* T-SQL code by https://expert-only.com */ SELECT * FROM Employees WHERE Age > 35 AND Department = 'IT';
This query returns the employees who are over 35 years old and who work in the IT department. It uses the “AND” logical operator to combine the conditions on age and department, using the clause WHERE Age > 35 AND Department = ‘IT’.
| IDEmployee | EmployeeName | Age | Department |
|---|---|---|---|
| 3 | Bob Johnson | 40 | IT |
7. T-SQL OR logical operator
The “OR” logic operator allows you to select rows that satisfy at least one of the filtering conditions in the WHERE clause of a query.
Simple query example
/* T-SQL code by https://expert-only.com */ SELECT * FROM Products WHERE Price < 500 OR ProductWeight > 15;
In this case, only the products having a price under five hundred dollars and a weight larger than 15 are displayed.
| ProductID | ProductName | Price | Weight |
|---|---|---|---|
| 1 | Computer | 1000 | 20 |
| 3 | Monitor | 300 | 15 |
Complex query example using the SQL OR operator
This query returns the names of customers who have placed less than 2 orders or more than 3 orders. It uses the “OR” logic operator to combine conditions on the number of orders, using the clause HAVING COUNT() < 2 OR COUNT() > 3.
/* T-SQL code by https://expert-only.com */
SELECT
c.CustomerName,
COUNT(*) as 'Number of orders'
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName
HAVING COUNT(*) < 2
OR COUNT(*) > 3;
In this second query using the OR SQL operator, we can see that only 2 customers have less than 2 or more then 3 orders.
| Name | Number of orders |
|---|---|
| Jane Doe | 1 |
| Bob Johnson | 1 |
8. The LIKE Logical Operator to Compare Strings
In T-SQL, to compare one string against specific values and know if it contains it, we use the LIKE keyword. Indeed, the “LIKE” string operator allows you to filter rows that contain a specific string.
You can use wildcard characters such as “%” (which matches any number of characters) and “_” (which matches a single character) in the string to search for.
It is possible to use regular expressions available with SQL Server in the LIKE clause for complex searches.
Simple example using LIKE comparison operator
/* T-SQL code by https://expert-only.com */ SELECT * FROM Customers WHERE CustomerName LIKE 'Ja%';
This query returns customers whose name starts with “J”. It uses the “LIKE” string operator and the “%” wildcard character to filter customers based on their name, using the clause WHERE Name LIKE ‘J%’.
| CustomerID | Name | Country |
|---|---|---|
| 2 | Jane Doe | Canada |
Complex example using LIKE
/* T-SQL code by https://expert-only.com */ SELECT * FROM Customers WHERE CustomerName LIKE 'J%' OR Country LIKE 'C%';
SELECT * FROM Customers WHERE Name LIKE ‘J%’ OR Country LIKE ‘C%’; This query returns customers whose name starts with “J” or who live in a country whose name starts with “C”. It uses the “LIKE” string operator and the “%” and “_” wildcard characters to filter customers based on their name and country, using the clause WHERE Name LIKE ‘J%’ OR Country LIKE ‘C%’. The result is as follows:
| CustomerID | Name | Country |
|---|---|---|
| 1 | John Smith | USA |
| 2 | Jane Doe | Canada |
9. SQL Server CONTAINS function to search text
The “CONTAINS” function in SQL Server is a powerful tool for text search within a column. It enables you to find specific text fragments in your data.
However, for this function to work properly, a full-text index must be created on the column you want to search.
Also if the Full-Search Feature is not installed on your SQL Server you may face an error.
In essence, it would be something like this code, with 3 simple steps.
/* T-SQL code by https://expert-only.com */
-- 1. Create the index
CREATE UNIQUE INDEX ui_CustomerID
ON Customers(CustomerID);
-- 2. Create the full text catalog
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
-- 3. Create the full text index
CREATE FULLTEXT INDEX ON Customers
(
CustomerName LANGUAGE English
)
KEY INDEX ui_CustomerID
ON ftCatalog
WITH CHANGE_TRACKING AUTO;
First example using the CONTAINS SQL function
This query’s goal is to find all customers whose name contains ‘John’ or ‘Smith’:
SELECT * FROM Customers WHERE CONTAINS(CustomerName, 'John OR Smith');
More complex example using the T-SQL predicate
A second query, this time to find all orders that have ‘John’ and ‘USA’ in the customer’s name and country fields respectively, but not ‘Smith’:
SELECT *
FROM Orders o
JOIN Customers c
ON o.CustomerID = c.CustomerID
WHERE CONTAINS(c.CustomerName, 'John AND USA AND NOT Smith');
Conclusion about SQL comparisons
Some T-SQL operators are very useful to compare values, whether they are stored in columns or in variables. They can be used with logic operators like AND and OR to combine multiple filter conditions.
Choose comparison and logic operators carefully to get the desired result, while also ensuring to avoid syntax errors. It is also recommended to test the performance of queries using different operators to make sure the query runs efficiently.
It is also recommended to create indexes on columns that are often used in filter conditions, in order to improve query performance. However, it is important not to overload the database with too many indexes, as this can also slow down performance.
In general, it is important to plan queries well and choose the right operators to achieve the desired result efficiently.
Be the first to comment