Microsoft SQL Server

In Microsoft SQL Server, comparison operators are used to compare values within SQL queries. These operators help you filter data based on specific conditions by comparing values from columns with constants or other column values. Comparison operators include =, !=, >, <, >=, and <=. They are essential for writing conditional statements in WHERE clauses, and learning how to use them is crucial for retrieving precise data from your database.

Below is a detailed explanation of each comparison operator with examples and best practices.

1. Equal to (=)

The equal to operator = is used to filter rows where the column value matches the specified value exactly.

SELECT column_name
FROM table_name
WHERE column_name = value;

Example:

SELECT ProductName, Price
FROM Products
WHERE Category = 'Electronics';

This query retrieves all products from the Products table that belong to the Electronics category.

2. Not Equal to (!= or <>)

The not equal to operator (!= or <>) is used to filter rows where the column value does not match the specified value.

SELECT column_name
FROM table_name
WHERE column_name != value;

Example:

SELECT ProductName, Price
FROM Products
WHERE Category != 'Clothing';

This query retrieves all products that do not belong to the Clothing category.

3. Greater Than (>)

The greater than operator > filters rows where the column value is greater than the specified value.

SELECT column_name
FROM table_name
WHERE column_name > value;

Example:

SELECT ProductName, Price
FROM Products
WHERE Price > 100;

This query retrieves all products with a price greater than 100.

4. Less Than (<)

The less than operator < filters rows where the column value is less than the specified value.

SELECT column_name
FROM table_name
WHERE column_name < value;

Example:

SELECT ProductName, Price
FROM Products
WHERE Price < 50;

This query retrieves all products with a price less than 50.

5. Greater Than or Equal to (>=)

The greater than or equal to operator >= filters rows where the column value is greater than or equal to the specified value.

SELECT column_name
FROM table_name
WHERE column_name >= value;

Example:

SELECT ProductName, Price
FROM Products
WHERE Price >= 100;

This query retrieves all products with a price of 100 or more.

6. Less Than or Equal to (<=)

The less than or equal to operator <= filters rows where the column value is less than or equal to the specified value.

SELECT column_name
FROM table_name
WHERE column_name <= value;

Example:

SELECT ProductName, Price
FROM Products
WHERE Price <= 50;

This query retrieves all products with a price of 50 or less.

7. Combining Comparison Operators with AND and OR

You can combine comparison operators using logical operators such as AND and OR to create more complex conditions.

SELECT ProductName, Price
FROM Products
WHERE Price > 50 AND Category = 'Electronics';

This query retrieves all electronic products with a price greater than 50.

SELECT ProductName, Price
FROM Products
WHERE Price < 50 OR Category = 'Clothing';

This query retrieves all products that are either priced less than 50 or belong to the Clothing category.

8. Best Practices for Using Comparison Operators

1. Use = for Exact Matches – Use the = operator for comparing exact values when you need to find specific records, such as retrieving products from a particular category.

   SELECT * 
   FROM Customers 
   WHERE Country = 'USA';

2. Use != or <> for Excluding Values – Use != or <> when you need to exclude specific values from the result set.

   SELECT * 
   FROM Products 
   WHERE Category != 'Furniture';

3. Be Careful with Greater/Less Than Operators – When using > and <, ensure the data type of the columns being compared is numeric or compatible for the comparison (e.g., dates). Always consider the range you're filtering.

   SELECT * 
   FROM Sales 
   WHERE SaleDate > '2023-01-01';

4. Combine with AND/OR for Complex Conditions – You can combine multiple comparison operators using logical operators to filter data more precisely.

   SELECT * 
   FROM Orders 
   WHERE TotalAmount >= 500 AND Status = 'Shipped';

5. Avoid Comparing Incompatible Data Types – Ensure the data types being compared are compatible. Comparing strings with numbers, for example, may lead to errors.

6. Index Columns for Performance – If you're frequently using comparison operators on certain columns, consider indexing those columns to improve query performance, especially for large datasets.

By mastering comparison operators in SQL Server, you can write efficient and precise queries to retrieve data that meets specific conditions, which is crucial for filtering and analyzing data effectively.

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

SELECT *
FROM org_client
WHERE gender = 'F';

SELECT *
FROM org_client
WHERE gender <> 'F';

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

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

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

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

SELECT *
FROM org_client
WHERE birth_year BETWEEN 1985 AND 1995;

SELECT *
FROM org_employee
WHERE designation IN ('Sales Manager', 'Financial Analyst', 'CFO');

SELECT *
FROM org_client
WHERE last_name LIKE 'Me%';

SELECT *
FROM org_client
WHERE first_name NOT LIKE '%e';

SELECT *
FROM act_order
WHERE shipped_date IS NULL;

SELECT *
FROM act_order
WHERE shipped_date IS NOT NULL;

SELECT *
FROM org_employee
WHERE designation NOT IN ('Sales Manager', 'Financial Analyst', 'CFO');

Equal To (=)

SELECT * FROM org_client WHERE gender = 'F';

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.

IN OPERATOR

SELECT * FROM org_employee WHERE designation IN ('Sales Manager', 'Financial Analyst', 'CFO');

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.

BETWEEN OPERATOR

SELECT * FROM act_order WHERE order_date BETWEEN '2023-12-10' AND '2023-12-17';

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.

LIKE OPERATOR

SELECT * FROM org_client WHERE last_name LIKE 'Me%';

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. Retrieve entries for clients with last names that begin with 'Me%', where '%' signifies the possibility of zero or more characters following 'Me'.

IS NULL OPERATOR

SELECT * FROM prd_product WHERE description IS NULL;

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 description value for product id 7 and 9 compose a empty string, this is not a null.