How to compare values using logical operators in T-SQL?

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.

ClientIDNameCountry
1John SmithUSA

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.

ClientIDNameCountry
2Jane DoeCanada

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;

ProductIDProductNamePriceWeight
1Computer100020

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.

NameNumber of orders
John Smith2

To go further on manipulating data using SQL Server code, these examples help you understand the different data types available in SQL.

SQL Server data types with code examples to create columns

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;
EmployeeIDEmployeeNameAgeDepartment
1John Smith35IT
3Bob Johnson40IT

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’.

IDEmployeeEmployeeNameAgeDepartment
1John Smith35IT
3Bob Johnson40IT

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.

ProductIDProductNamePriceWeight
1Computer100020
3Screen30015

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.

NameNumber of orders
Jane Doe1
Bob Johnson1

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%’.

CustomerIDNameCountry
1John SmithUSA

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:

CustomerIDNameCountry
1John SmithUSA
2Jane DoeCanada

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.

Create a SQL Server full-text index column with a script

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.

SQL Server data types with code examples to create columns

Leave a Comment

Your email address will not be published. Required fields are marked *