Microsoft SQL Server

In Microsoft SQL Server, the > (greater than) and < (less than) operators are used in the WHERE clause to filter data based on comparisons between numerical, date, or other comparable values. These operators allow you to retrieve records where a column's value is either greater than or less than a specified value. For beginners, mastering these comparison operators is crucial for writing queries that filter data based on ranges and thresholds.

Below is a detailed explanation of how to use the > and < operators, along with examples and best practices.

1. Basic Syntax of > (Greater Than) Operator

The > operator retrieves rows where the column's value is greater than a specified value.

SELECT column_name 
FROM table_name 
WHERE column_name > value;

• Replace column_name with the column you want to compare.
• Replace table_name with the actual table name.
• Replace value with the number, date, or string for comparison.

Example:

SELECT ProductName, Price 
FROM Products 
WHERE Price > 50;

This query retrieves all products from the Products table where the price is greater than 50.

2. Basic Syntax of < (Less Than) Operator

The < operator retrieves rows where the column's value is less than a specified value.

SELECT column_name 
FROM table_name 
WHERE column_name < value;

Example:

SELECT ProductName, StockQuantity 
FROM Products 
WHERE StockQuantity < 100;

This query retrieves all products where the stock quantity is less than 100.

3. Using > and < with Dates

These operators can also be used to compare date values, making it easy to filter records based on time.

SELECT OrderID, OrderDate 
FROM Sales 
WHERE OrderDate > '2023-01-01';

This query retrieves all sales orders made after January 1, 2023.

SELECT OrderID, OrderDate 
FROM Sales 
WHERE OrderDate < '2023-06-30';

This query retrieves all sales orders made before June 30, 2023.

4. Combining > and < with AND

You can combine the > and < operators using the AND operator to create a range condition.

SELECT ProductName, Price 
FROM Products 
WHERE Price > 50 AND Price < 200;

This query retrieves all products where the price is between 50 and 200.

5. Using > and < with Other Operators

You can also combine > and < with other comparison operators such as >= (greater than or equal to) and <= (less than or equal to) for more precise filtering.

SELECT ProductName, Price 
FROM Products 
WHERE Price >= 50 AND Price <= 200;

This query retrieves products where the price is between 50 and 200, inclusive.

6. Best Practices for Using > and <

1. Use > and < for Efficient Data Filtering – Use the > and < operators to filter large datasets efficiently, especially when working with numerical or date data.

   SELECT * 
   FROM Sales 
   WHERE SaleAmount > 500;

2. Be Cautious with Date Formats – When comparing dates, ensure that the date format is consistent and matches the format supported by SQL Server (e.g., 'YYYY-MM-DD') to avoid unexpected results.

3. Consider Indexing for Performance – If you frequently query using > and < operators on large tables, make sure that the columns being compared (such as date or price) are indexed for better performance.

4. Use Ranges for More Precise Queries – Combining > and < with AND allows you to create ranges, which can be particularly useful for retrieving data within specific intervals.

   SELECT * 
   FROM Customers 
   WHERE CustomerID > 100 AND CustomerID < 200;

5. Test Your Queries with Different Data Types – Test how > and < behave with different data types like numbers, dates, and strings. Keep in mind that string comparisons are based on alphabetical order.

By mastering the > and < operators, you can create flexible queries that efficiently retrieve records based on specific criteria, making your SQL queries more powerful and effective.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT *
FROM (
SELECT *
FROM org_employee
WHERE salary > 150000
) AS filtered_employees
ORDER BY salary;

SELECT *
FROM (
SELECT *
FROM org_employee
WHERE salary >= 150000
) AS filtered_employees
ORDER BY salary;

SELECT *
FROM act_order
WHERE CONVERT(date, order_date) >= '2023-12-15'
ORDER BY order_date;

Example 1:

Let's explore the procedure of extracting information from a designated table using the '>' SQL operator with numeric data. This query retrieves all rows from the employee table where the salary column is greater than 150k.

Example 1 - Raw data from employee table

Example 1 - Query

SELECT * FROM org_employee WHERE salary > 150000 ORDER BY salary;

Example 1 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query. Note that salary 150,000 is not selected.

Example 1 - Query Output

Example 2:

Let's explore the procedure for extracting information from a designated table using '>=' operator on integer data. This query retrieves all rows from the employee table where the salary column is greater than 150k, including border line salary of 150k.

Example 2 - Raw data from employee table

Example 2 - Query

SELECT * FROM org_employee WHERE salary >= 150000 ORDER BY salary;

Example 2 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query. Note that salary 150,000 is also selected, when compared to above query where 150,000 was not selected.

Example 2 - Query Output

Example 3:

Let's explore the procedure for extracting information from a designated table using the '>=' SQL operator on date time data. This query retrieves all rows from the orders table where the order date is greater than or equal to '2023-12-15'

Example 3 - Raw data from order table

Example 3 - Query

SELECT * FROM act_order WHERE order_date >= '2023-12-15' ORDER BY order_date;

Example 3 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 3 - Query Output