T-SQL queries with examples using the most common T-SQL logical comparison operators.
Here is a list with the comparison and logic operators commonly used in T-SQL language, with examples of queries. 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 section.
Prepare the Tables Used by T-SQL Operators
The 4 tables used here are as follows, they are deliberately basic to easily illustrate the cases.
- Customers
- Orders
- Products
- Employees
A. Create the Structure of Tables in T-SQL
Run this code from an SSMS window to add the tables to your database.
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), Weight DECIMAL(10,2) ); CREATE TABLE Employees ( EmployeeID INT, EmployeeName VARCHAR(255), Age INT, Department VARCHAR(255) );
B. Insert Data into Each Table
Copy, paste and run these lines to insert a few rows into each of the 4 tables, still using the SSMS software.
INSERT INTO Customers (CustomerID, Name, 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, Weight) 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 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
1. Equal comparison operator
The comparison operator “=”, equal in English, is used to compare if two values are equal. It returns true or false.
Simple example
SELECT * FROM Clients WHERE ClientID = 1;
The result is a single row with client number 1, John Smith.
| ClientID | Name | Country |
|---|---|---|
| 1 | John Smith | USA |
Complex example
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:
SELECT MAX(OrderDate) FROM Orders;
2. SQL Server comparison operators 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.
SELECT MAX(OrderDate) FROM Orders;
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.
SELECT *
FROM Clients c
JOIN Orders o
ON c.ClientID = o.ClientID
JOIN Products p
ON o.OrderID = p.ProductID
WHERE c.Country <> 'USA'
AND p.Price > 500;
3. 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
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.
SELECT c.Name, COUNT() as 'Number of orders' FROM Clients c JOIN Orders o ON c.ClientID = o.ClientID GROUP BY c.Name 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.
4. 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
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.
SELECT AVG(Price) as 'Average price' FROM Products WHERE Weight >= 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.
5. 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
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 |
|---|---|---|---|
| 1 | John Smith | 35 | IT |
| 3 | Bob Johnson | 40 | IT |
6. 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
SELECT * FROM Products WHERE Price < 500 OR Weight > 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 | Screen | 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.
SELECT
c.Name,
COUNT() as 'Number of orders'
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.Name
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 |
7. Logical Operator for Comparing Strings with LIKE
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
SELECT * FROM Customers WHERE Name LIKE 'J%';
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 |
|---|---|---|
| 1 | John Smith | USA |
Complex example using LIKE
SELECT * FROM Customers WHERE Name 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 |
8. SQL Server logical function to search text using Contains
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. To do so, follow the steps in this article.
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 on SQL Server comparison operators
T-SQL comparison operators are useful tools for filtering rows from a table based on various conditions. They can be used with logic operators like AND and OR to combine multiple filter conditions. It is important to 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 advisable 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.
