Sql Comparison Operators Complete Guide

 Last Update:2025-06-23T00:00:00     .NET School AI Teacher - SELECT ANY TEXT TO EXPLANATION.    8 mins read      Difficulty-Level: beginner

Understanding the Core Concepts of SQL Comparison Operators

SQL Comparison Operators

1. Equal to ( = )

  • Usage: Compares two values for equality.
  • Example: 
    SELECT * FROM Employees WHERE department = 'Sales';
  • Info: This operator checks if the values on both sides of the operator are the same.

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

  • Usage: Compares two values to check for inequality.
  • Example: 
    SELECT * FROM Employees WHERE department <> 'Sales';
  • Info: Unlike the Equal to operator, this operator returns true if the values are different. Both <> and != are used, though <> is supported by all SQL standards.

3. Greater Than ( > )

  • Usage: Checks if one value is greater than another.
  • Example: 
    SELECT * FROM Employees WHERE salary > 50000;
  • Info: This operator is useful for filtering data where numerical values need to be larger than a certain threshold.

4. Less Than ( < )

  • Usage: Checks if one value is less than another.
  • Example: 
    SELECT * FROM Employees WHERE salary < 50000;
  • Info: Contrary to the Greater Than operator, this operator is used when values need to be smaller than a certain threshold.

5. Greater Than or Equal to ( >= )

  • Usage: Checks if one value is greater than or equal to another.
  • Example: 
    SELECT * FROM Employees WHERE salary >= 50000;
  • Info: This operator helps in scenarios where values need to be either greater than or exactly equal to a specified value.

6. Less Than or Equal to ( <= )

  • Usage: Checks if one value is less than or equal to another.
  • Example: 
    SELECT * FROM Employees WHERE salary <= 50000;
  • Info: This operator is used when values need to be either less than or exactly equal to a specified value.

7. IS NULL

  • Usage: Checks if a value is NULL.
  • Example: 
    SELECT * FROM Employees WHERE manager_id IS NULL;
  • Info: This operator is crucial for checking for missing or unknown values in a column. Note that NULL is not the same as zero or an empty string; it represents a non-existent or unknown value.

8. IS NOT NULL

  • Usage: Checks if a value is not NULL.
  • Example: 
    SELECT * FROM Employees WHERE manager_id IS NOT NULL;
  • Info: This is the inverse of the IS NULL operator and is used to filter out rows where the column has a value.

9. LIKE

  • Usage: Searches for a specified pattern in a column.
  • Example: 
    SELECT * FROM Employees WHERE name LIKE 'A%';
  • Info: The % wildcard represents zero, one, or multiple characters. The _ wildcard represents a single character. LIKE is used for pattern matching and text searches.

10. IN

  • Usage: Checks if a value exists within a set of values.
  • Example: 
    SELECT * FROM Employees WHERE department IN ('Sales', 'Marketing', 'HR');
  • Info: This operator is used to filter records based on multiple discrete values.

11. BETWEEN

  • Usage: Selects values within a given range.
  • Example: 
    SELECT * FROM Employees WHERE salary BETWEEN 40000 AND 60000;
  • Info: The BETWEEN operator selects values between two values, inclusive. It simplifies queries that would otherwise require multiple AND conditions.

12. NOT BETWEEN

  • Usage: Selects values outside a given range.
  • Example: 
    SELECT * FROM Employees WHERE salary NOT BETWEEN 40000 AND 60000;
  • Info: This is the inverse of the BETWEEN operator and selects values outside the specified range.

Conclusion

Understanding SQL comparison operators is crucial for effective data manipulation and querying. These operators enable you to construct complex queries that can filter, sort, and compare data according to specific criteria. Proper use of these operators enhances the efficiency and accuracy of data retrieval and analysis.

Online Code run

🔔 Note: Select your programming language to check or run code at

💻 Run Code Compiler

Step-by-Step Guide: How to Implement SQL Comparison Operators 

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    Salary DECIMAL(10, 2),
    Department VARCHAR(50)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Salary, Department) VALUES
(1, 'John', 'Doe', 30, 50000.00, 'Sales'),
(2, 'Jane', 'Smith', 25, 45000.00, 'Marketing'),
(3, 'Emily', 'Jones', 40, 60000.00, 'Sales'),
(4, 'Michael', 'Brown', 35, 55000.00, 'Engineering'),
(5, 'Sarah', 'White', 28, 50000.00, 'Marketing');

SQL Comparison Operators

1. Equal To (=)

This operator checks if two values are equal.

Example: Find all employees who work in the 'Sales' department.

SELECT * FROM Employees WHERE Department = 'Sales';

Output:

| EmployeeID | FirstName | LastName | Age | Salary | Department | |------------|-----------|----------|-----|-----------|------------| | 1 | John | Doe | 30 | 50000.00 | Sales | | 3 | Emily | Jones | 40 | 60000.00 | Sales |

2. Not Equal To (<>)

This operator checks if two values are not equal.

Example: Find all employees who do not work in the 'Marketing' department.

SELECT * FROM Employees WHERE Department <> 'Marketing';

Output:

| EmployeeID | FirstName | LastName | Age | Salary | Department | |------------|-----------|----------|-----|-----------|-------------| | 1 | John | Doe | 30 | 50000.00 | Sales | | 3 | Emily | Jones | 40 | 60000.00 | Sales | | 4 | Michael | Brown | 35 | 55000.00 | Engineering |

3. Greater Than (>)

This operator checks if one value is greater than another.

Example: Find all employees older than 30.

SELECT * FROM Employees WHERE Age > 30;

Output:

| EmployeeID | FirstName | LastName | Age | Salary | Department | |------------|-----------|----------|-----|-----------|------------| | 3 | Emily | Jones | 40 | 60000.00 | Sales |

4. Less Than (<)

This operator checks if one value is less than another.

Example: Find all employees earning less than $55,000.

SELECT * FROM Employees WHERE Salary < 55000.00;

Output:

| EmployeeID | FirstName | LastName | Age | Salary | Department | |------------|-----------|----------|-----|-----------|-------------| | 1 | John | Doe | 30 | 50000.00 | Sales | | 2 | Jane | Smith | 25 | 45000.00 | Marketing | | 5 | Sarah | White | 28 | 50000.00 | Marketing |

5. Greater Than or Equal To (>=)

This operator checks if one value is greater than or equal to another.

Example: Find all employees earning $50,000 or more.

SELECT * FROM Employees WHERE Salary >= 50000.00;

Output:

| EmployeeID | FirstName | LastName | Age | Salary | Department | |------------|-----------|----------|-----|-----------|-------------| | 1 | John | Doe | 30 | 50000.00 | Sales | | 3 | Emily | Jones | 40 | 60000.00 | Sales | | 4 | Michael | Brown | 35 | 55000.00 | Engineering | | 5 | Sarah | White | 28 | 50000.00 | Marketing |

6. Less Than or Equal To (<=)

This operator checks if one value is less than or equal to another.

Example: Find all employees 28 years old or younger.

SELECT * FROM Employees WHERE Age <= 28;

Output:

| EmployeeID | FirstName | LastName | Age | Salary | Department | |------------|-----------|----------|-----|-----------|-------------| | 2 | Jane | Smith | 25 | 45000.00 | Marketing | | 5 | Sarah | White | 28 | 50000.00 | Marketing |

Combining Comparison Operators

You can also combine multiple comparison operators using AND and OR to create more complex queries.

Example: Find all employees in the 'Sales' department who are older than 30.

SELECT * FROM Employees WHERE Department = 'Sales' AND Age > 30;

Output:

| EmployeeID | FirstName | LastName | Age | Salary | Department | |------------|-----------|----------|-----|-----------|------------| | 3 | Emily | Jones | 40 | 60000.00 | Sales |

Example: Find all employees who are in either the 'Engineering' or 'Marketing' department.

SELECT * FROM Employees WHERE Department = 'Engineering' OR Department = 'Marketing';

Output:

| EmployeeID | FirstName | LastName | Age | Salary | Department | |------------|-----------|----------|-----|-----------|-------------| | 2 | Jane | Smith | 25 | 45000.00 | Marketing | | 4 | Michael | Brown | 35 | 55000.00 | Engineering | | 5 | Sarah | White | 28 | 50000.00 | Marketing |

Top 10 Interview Questions & Answers on SQL Comparison Operators

Top 10 Questions and Answers on SQL Comparison Operators

1. What are SQL Comparison Operators?

2. How do you use the = operator in SQL?

Answer: The = operator is used to check if two values are equal. It's often used in the WHERE clause to filter records based on equality conditions. For example:

SELECT * FROM employees WHERE department = 'Sales';

This query selects all records from the employees table where the department column equals 'Sales'.

3. What does the <> operator represent in SQL?

Answer: The <> operator (or alternatively, !=) represents "not equal to." It is used to filter out records in the WHERE clause that do not match a specified value. Example:

SELECT * FROM products WHERE category <> 'Electronics';

Here, the products table will return all products that are not in the 'Electronics' category.

4. How can the BETWEEN operator be utilized in SQL queries?

Answer: The BETWEEN operator selects values within a given range, inclusive of the start and end values. Used with the WHERE clause. Example:

SELECT * FROM orders WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31';

This statement fetches all orders made in the year 2021, including those placed on January 1st and December 31st.

5. Can you explain how the LIKE operator works in SQL, maybe with an example?

Answer: The LIKE operator is used for pattern matching in SQL queries. It allows you to search for a specified pattern in a column. Commonly used wildcards include % and _. % matches any sequence of characters, while _ matches exactly one character.

Example:

SELECT * FROM customers WHERE last_name LIKE 'S%';

This will return all customers whose last name starts with the letter 'S'.

Another example:

SELECT * FROM products WHERE product_name LIKE '_earphone%';

This would return all products whose names have 'earphone' starting from the second character.

6. What is the difference between IN and = operators in SQL?

Answer: While both the = and IN operators are used to filter data based on specific values, they serve different purposes. = checks for equality against a single specified value, whereas IN checks for equality across a set of values.

Example:

-- Using =
SELECT * FROM orders WHERE customer_id = 123;
-- This returns all orders from a single customer with ID 123

-- Using IN
SELECT * FROM customers WHERE country IN ('USA', 'Canada', 'Mexico');
-- This returns all customers from the USA, Canada, or Mexico.

7. How do you use the IS NULL and IS NOT NULL operators in SQL?

Answer: These operators are crucial for dealing with columns containing NULL values, which indicate unknown or missing data. IS NULL is used to filter records where the column is NULL, while IS NOT NULL filters records where the column contains specific data.

Example using IS NULL:

SELECT * FROM employees WHERE termination_date IS NULL;

This query returns all active employees with an undefined (NULL) termination date.

Example using IS NOT NULL:

SELECT * FROM projects WHERE completion_date IS NOT NULL;

This statement retrieves details of all completed projects where there exists a completion_date.

8. Could you provide an example of using the BETWEEN operator with dates?

Answer: The BETWEEN operator is particularly useful when working with datetime fields like birthdates, hire dates, etc. Here’s an example involving dates:

SELECT * FROM employees 
WHERE hire_date BETWEEN '2019-01-01' AND '2020-12-31';

This SQL query selects all employees hired within the calendar years of 2019 and 2020.

9. How do you use the LIKE operator with multiple patterns?

Answer: You can use the LIKE operator multiple times in conjunction with the OR keyword to match against several patterns. Alternatively, regular expressions are available in more advanced SQL dialects for complex pattern matching.

Example:

SELECT * FROM employees 
WHERE first_name LIKE 'Jo%' OR last_name LIKE 'S%';

This query retrieves employees where either the first name starts with 'Jo' (e.g., John, Joe, Joel) or where the last name begins with 'S' (e.g., Smith, Scott).

10. What does the <> operator do if either operand is NULL?

You May Like This Related .NET Topic

📥 Download Code ▶️ Watch Online 📖 Read Description

Login to post a comment.