Comparison Operators in SQL Server: SQL AND, OR, BETWEEN, LIKE....

Not Equal Operator (<> or !=):it is used to compare two values for inequality.
Example: find all students who are not 19 years old.
Syntax:
SELECT * FROM Students WHERE age <> 19;
OR
SELECT * FROM Students WHERE age != 19;
Both syntaxes will give the same results, either use “<>” or “!=”

Result:

Greater than Operator (>):it is used to compare if one value is greater than another.
Example: find all students who have a grade higher than 3.6.
Syntax:

SELECT * FROM students WHERE grade > 3.6;

Less than Operator (<):used to compare if one value is less than another.
Example: find all students who have a grade lower than 3.5
Syntax:

SELECT * FROM students WHERE grade < 3.5;

Less than or equal to Operator (<=):used to compare if one value is less than or equal to another.
Example: find all students who have a grade of 3.5 or lower.
Syntax:

SELECT * FROM Students WHERE grade <= 3.5;

Like Operator:Used to compare if a value matches a pattern.
Suppose we want to find all the students whose name starts with “J”. We can use the following query:
SELECT * FROM Students WHERE name LIKE 'J%';
Result:

Now, we want to find all the students whose name ends with “e”. We can use the following query:

SELECT * FROM Students WHERE name LIKE '%e';

Now, suppose we want to find all the students whose name second letter is “a”. We can use the following query:

SELECT * FROM students WHERE name LIKE '_a%';

Now, we want to find all the students whose name’s second last letter is “i”. We can use the following query:
SELECT * FROM students WHERE name LIKE ‘%i_’;

Result:

We will find all the students whose name starts with “M” and ends with “e”. We can use the following query:

SELECT * FROM students WHERE name LIKE 'M%e';

NOT LIKE:

Not like operator is used to specify a pattern to exclude from a search.

SELECT * FROM students
WHERE name NOT LIKE '%a%';

Between:
It is used to compare if a value is between two specified values.
Suppose we want to find all the students whose age is between 18 and 20. We can use the following query:

SELECT * FROM Students
WHERE age BETWEEN 18 AND 20;

NOT BETWEEN:

Used to specify a range of values to exclude from a search.

SELECT * FROM Students
WHERE age NOT BETWEEN 18 AND 20;

IN operator:

It is used to compare if a value matches any value in a list of values.

SELECT * FROM Students
WHERE name IN ('Emily', 'John');

NOT IN:

Used to specify a list of values to exclude from a search.

SELECT * FROM Students
WHERE age NOT IN (18, 17);

IS NULL and IS NOT NULL operator:This operator, used to check if a value is null or not null.
Suppose we want to find all the students who have not yet been assigned a grade. We can use the following query:

Since the “Students” table does not have any null values, the query does not return any results.

Result:

IS NOT NULL:

SELECT * FROM Students
WHERE grade IS NOT NULL;

ALL:ALL is a keyword that is used to compare a value with all the values in a list or a subquery. It returns true if the comparison is true for all the values in the list or subquery, and false otherwise.
Suppose we have an “orders” table with the following columns: “order_id”, “product_name”, “quantity”, “price”.
To demonstrate the ALL operator, let’s insert some sample data into the table:

CREATE TABLE Orders (
order_id INT PRIMARY KEY, 
product_name VARCHAR(50), 
quantity INT, 
price DECIMAL(10,2));

INSERT INTO Orders VALUES 
(1, 'Product A', 10, 100.00), 
(2, 'Product B', 5, 50.00), 
(3, 'Product C', 15, 75.00), 
(4, 'Product D', 20, 80.00), 
(5, 'Product E', 8, 120.00);

 Now let’s say we want to find all the orders with a quantity greater than or equal to the maximum quantity in the “orders” table. We can use the ALL operator in the following query:

Result:

ANY: Used to specify that at least one value in a subquery must meet a certain condition. 

Example using ANY: 
SELECT product_name, quantity, price FROM Orders 
WHERE price > ANY (SELECT price FROM Orders WHERE quantity < 10);
In this example, the query selects the product_name, quantity, and price columns from the "Orders" table where the price is greater than any of the prices from orders where the quantity is less than 10. 
Result: 

It means that the query returns all rows from "orders" where the price is greater than to the price of any order, whose quantity is less than 10. 
EXISTS: Exists operator is used to check if a subquery returns any rows. 
Let’s create a table for understand the Exists Operator. 
CREATE TABLE Customers (
customer_id INT PRIMARY KEY, 
customer_name VARCHAR(50), 
city VARCHAR(50), 
state VARCHAR(50));

INSERT INTO Customers VALUES 
(1, 'John Doe', 'New York', 'NY'), 
(2, 'Jane Smith', 'Los Angeles', 'CA'), 
(3, 'Bob Johnson', 'Chicago', 'IL'), 
(4, 'Alice Lee', 'San Francisco', 'CA'), 
(5, 'Mike Brown', 'Houston', 'TX'), 
(6, 'Sarah Lee', 'San Francisco', 'CA'), 
(7, 'David Kim', 'New York', 'NY');
Now let's say we want to check if there are any customers from the state of "TX". We can use Now let's say we want to check if there are any customers from the state of "TX". We can use the EXISTS operator for this: 
SELECT * FROM Customers
WHERE EXISTS (SELECT * FROM Customers WHERE state = 'TX');

This will return all the rows from the "customers" table where there exists at least one row where the state is "TX". In this case, it will return only one row (customer_id 5, Mike Brown). 

Interview related questions: 
What is the difference between the LIKE operator and the = operator in SQL? 
How does the BETWEEN operator work in SQL? 
What is the difference between the IN and NOT IN operators in SQL? 
How can you check for NULL values in SQL using comparison operators? 
What is the difference between the ANY and ALL operators in SQL? 
What is the purpose of the EXISTS operator in SQL? 
Can you combine comparison operators in SQL? If so, how? 
What is the order of precedence for comparison operators in SQL? 
How does the NOT operator affect comparison operators in SQL? 
What is the difference between the UNION and UNION ALL operators in SQL? 
Conclusion:
 In SQL Server, comparison operators are used to compare values between two expressions or operands. There are different types of comparison operators like equal to (=), not equal to (<> or !=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), LIKE, BETWEEN, IN, IS NULL/IS NOT NULL, ALL, ANY/SOME, and EXISTS. These operators are very useful when it comes to filtering data from the database tables. They help to retrieve specific data based on certain conditions. For example, the LIKE operator is used to retrieve data based on a specific pattern or string match, the BETWEEN operator is used to retrieve data between two values, the IN operator is used to retrieve data that matches a set of values, and the EXISTS operator is used to retrieve data that exists in a subquery. Interviewers often ask questions related to comparison operators during SQL Server interviews to assess the candidate's knowledge of SQL queries and their ability to retrieve specific data from a database table based on certain conditions.