SQL Tutorial 10 Comparison Operators
SQL (Structured Query Language) uses comparison operators to compare values in different expressions. These operators are fundamental for filtering data and creating conditions for queries. Here are the commonly used comparison operators in SQL:
Equal: = (Checks if two values are equal)
Not equal: <> or != (Checks if two values are not equal)
Greater than: > (Checks if the left operand is greater than the right operand)
Less than: < (Checks if the left operand is less than the right operand)
Greater than or equal to: >= (Checks if the left operand is greater than or equal to the right operand)
Less than or equal to: <= (Checks if the left operand is less than or equal to the right operand)
Like: (Performs pattern matching with wildcard characters, often used with strings)
In: (Checks if a value matches any value in a list)
Not Like: (Negation of the Like operator)
Not In: (Negation of the In operator)
Between: (Checks if a value is within a range)
Not Between: (Negation of the Between operator)
Is Null: (Checks if a value is NULL)
Is Not Null: (Checks if a value is not NULL)
These operators are often used in the WHERE clause of SQL queries to filter data based on certain conditions. For example:
SELECT * FROM table_name WHERE column_name = value;
SELECT * FROM table_name WHERE column_name > value;
SELECT * FROM table_name WHERE column_name LIKE ‘a%’;
SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);
SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT * FROM table_name WHERE column_name IS NULL;
It’s important to use these operators appropriately and understand the underlying data types in order to perform accurate comparisons.
SQL Equal to with example
In SQL, the equal to operator = is used to check if two values are equal. It is commonly used in the WHERE clause of a SELECT statement to filter rows based on a specific condition. Here is an example to illustrate the use of the equal to operator:
Suppose we have a table named students with the following structure:
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);
Let’s assume the table students contains the following data:
| id | name | age |
|—-|———–|—–|
| 1 | John | 20 |
| 2 | Emily | 22 |
| 3 | Michael | 21 |
| 4 | Jessica | 20 |
Now, if we want to retrieve the details of a student with a specific ID, for instance, ID 2, we can use the equal to operator in the WHERE clause:
SELECT * FROM students WHERE id = 2;
This query will return the following result:
| id | name | age |
|—-|——-|—–|
| 2 | Emily | 22 |
The query filters the rows based on the condition id = 2 and returns the details of the student with an ID of 2.
SQL Greater than with example
In SQL, the greater than operator (>) is used to compare two expressions. It is typically used in the WHERE clause to filter rows based on a specified condition. Here’s an example to illustrate the use of the greater than operator in SQL:
Suppose we have a table named products with the following structure:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50),
price DECIMAL(10, 2)
);
Let’s assume the products table contains the following data:
| product_id | product_name | price |
|————|————–|——-|
| 1 | Laptop | 800 |
| 2 | Smartphone | 600 |
| 3 | Tablet | 400 |
| 4 | Headphones | 100 |
Now, if we want to retrieve the products with a price higher than 500, we can use the greater than operator in the WHERE clause:
SELECT * FROM products WHERE price > 500;
This query will return the following result:
| product_id | product_name | price |
|————|————–|——-|
| 1 | Laptop | 800 |
| 2 | Smartphone | 600 |
The query filters the rows based on the condition price > 500 and retrieves the products whose prices are higher than 500.
SQL Less than with Example
In SQL, the less than operator (<) is used to compare two expressions. It is typically used in the WHERE clause to filter rows based on a specified condition. Here’s an example to illustrate the use of the less than operator in SQL:
Suppose we have a table named products with the following structure:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50),
price DECIMAL(10, 2)
);
Let’s assume the products table contains the following data:
| product_id | product_name | price |
|————|————–|——-|
| 1 | Laptop | 800 |
| 2 | Smartphone | 600 |
| 3 | Tablet | 400 |
| 4 | Headphones | 100 |
Now, if we want to retrieve the products with a price less than 500, we can use the less than operator in the WHERE clause:
SELECT * FROM products WHERE price < 500;
| product_id | product_name | price |
|————|————–|——-|
| 3 | Tablet | 400 |
| 4 | Headphones | 100 |
The query filters the rows based on the condition price < 500 and retrieves the products whose prices are less than 500.
SQL Greater than or equal to with example
In SQL, the greater than or equal to operator (>=) is used to compare two expressions, where the left operand is greater than or equal to the right operand.
It is typically used in the WHERE clause to filter rows based on a specified condition. Here’s an example to illustrate the use of the greater than or equal to operator in SQL:
Suppose we have a table named employees with the following structure:
Let’s assume the employees table contains the following data:
employee_id | employee_name | salary |
|————-|—————|——–|
| 1 | John | 60000 |
| 2 | Emily | 70000 |
| 3 | Michael | 55000 |
| 4 | Jessica | 80000 |
Now, if we want to retrieve the employees with a salary greater than or equal to 60000, we can use the greater than or equal to operator in the WHERE clause:
SELECT * FROM employees WHERE salary >= 60000;
This query will return the following result:
| employee_id | employee_name | salary |
|————-|—————|——–|
| 1 | John | 60000 |
| 2 | Emily | 70000 |
| 4 | Jessica | 80000 |
The query filters the rows based on the condition salary >= 60000 and retrieves the employees whose salaries are greater than or equal to 60000.
Less than or equal to with Example
Certainly! The <= operator in SQL is used to check if a value is less than or equal to another value. It is commonly used in the WHERE clause of a SQL query to filter rows based on certain conditions. Here is an example that demonstrates the use of the less than or equal to operator:
Suppose we have a table named orders with the following structure:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE,
total_amount DECIMAL(10, 2)
);
Let’s assume the orders table contains the following data:
| order_id | order_date | total_amount |
|———-|————|————–|
| 1 | 2023-10-25 | 150.00 |
| 2 | 2023-10-26 | 200.00 |
| 3 | 2023-10-27 | 300.00 |
| 4 | 2023-10-28 | 250.00 |
If we want to retrieve the orders with a total amount less than or equal to 250.00, we can use the <= operator in the WHERE clause:
SELECT * FROM orders WHERE total_amount <= 250.00;
This query will return the following result:
| order_id | order_date | total_amount |
|———-|————|————–|
| 1 | 2023-10-25 | 150.00 |
| 2 | 2023-10-26 | 200.00 |
| 4 | 2023-10-28 | 250.00 |
The query filters the rows based on the condition total_amount <= 250.00 and retrieves the orders with a total amount less than or equal to 250.00.
SQL Not equal to with Example
In SQL, the not equal to operator (<> or !=) is used to compare two expressions to determine if they are not equal. It is commonly used in the WHERE clause of a SQL query to filter rows based on certain conditions. Here is an example that demonstrates the use of the not equal to operator:
Suppose we have a table named students with the following structure:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
age INT
);
Let’s assume the students table contains the following data:
| student_id | student_name | age |
|————|————–|—–|
| 1 | John | 20 |
| 2 | Emily | 22 |
| 3 | Michael | 21 |
| 4 | Jessica | 20 |
If we want to retrieve the students who are not 20 years old, we can use the not equal to operator in the WHERE clause:
SELECT * FROM students WHERE age <> 20;
This query will return the following result:
| student_id | student_name | age |
|————|————–|—–|
| 2 | Emily | 22 |
| 3 | Michael | 21 |
The query filters the rows based on the condition age <> 20 and retrieves the students whose age is not equal to 20. Both <> and != can be used to denote the not equal to operator in SQL.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50),
price DECIMAL(10, 2)
);
Let’s assume the products table contains the following data:
| product_id | product_name | price |
|————|————–|——-|
| 1 | Laptop | 800.00|
| 2 | Smartphone | 600.00|
| 3 | Tablet | 400.00|
| 4 | Headphones | 100.00|
Now, if we want to retrieve the products with a price less than or equal to 400, we can use the less than or equal to operator in the WHERE clause:
SELECT * FROM products WHERE price <= 400.00;
This query will return the following result:
| product_id | product_name | price |
|————|————–|——-|
| 3 | Tablet | 400.00|
| 4 | Headphones | 100.00|
The query filters the rows based on the condition price <= 400.00 and retrieves the products whose prices are less than or equal to 400.00.
Less than or equal to with Example
Certainly! The <= operator in SQL is used to check if a value is less than or equal to another value. It is commonly used in the WHERE clause of a SQL query to filter rows based on certain conditions. Here is an example that demonstrates the use of the less than or equal to operator:
Suppose we have a table named orders with the following structure:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE,
total_amount DECIMAL(10, 2)
);
Let’s assume the orders table contains the following data:
| order_id | order_date | total_amount |
|———-|————|————–|
| 1 | 2023-10-25 | 150.00 |
| 2 | 2023-10-26 | 200.00 |
| 3 | 2023-10-27 | 300.00 |
| 4 | 2023-10-28 | 250.00 |
If we want to retrieve the orders with a total amount less than or equal to 250.00, we can use the <= operator in the WHERE clause:
SELECT * FROM orders WHERE total_amount <= 250.00;
This query will return the following result:
| order_id | order_date | total_amount |
|———-|————|————–|
| 1 | 2023-10-25 | 150.00 |
| 2 | 2023-10-26 | 200.00 |
| 4 | 2023-10-28 | 250.00 |
The query filters the rows based on the condition total_amount <= 250.00 and retrieves the orders with a total amount less than or equal to 250.00.
SQL Not equal to with Example
In SQL, the not equal to operator (<> or !=) is used to compare two expressions to determine if they are not equal. It is commonly used in the WHERE clause of a SQL query to filter rows based on certain conditions. Here is an example that demonstrates the use of the not equal to operator:
Suppose we have a table named students with the following structure:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
age INT
);
Let’s assume the students table contains the following data:
| student_id | student_name | age |
|————|————–|—–|
| 1 | John | 20 |
| 2 | Emily | 22 |
| 3 | Michael | 21 |
| 4 | Jessica | 20 |
If we want to retrieve the students who are not 20 years old, we can use the not equal to operator in the WHERE clause:
SELECT * FROM students WHERE age <> 20;
This query will return the following result:
| student_id | student_name | age |
|————|————–|—–|
| 2 | Emily | 22 |
| 3 | Michael | 21 |
The query filters the rows based on the condition age <> 20 and retrieves the students whose age is not equal to 20. Both <> and != can be used to denote the not equal to operator in SQL.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE,
total_amount DECIMAL(10, 2)
);
Let’s assume the orders table contains the following data:
| order_id | order_date | total_amount |
|———-|————|————–|
| 1 | 2023-10-25 | 150.00 |
| 2 | 2023-10-26 | 200.00 |
| 3 | 2023-10-27 | 300.00 |
| 4 | 2023-10-28 | 250.00 |
If we want to retrieve the orders with a total amount less than or equal to 250.00, we can use the <= operator in the WHERE clause:
SELECT * FROM orders WHERE total_amount <= 250.00;
This query will return the following result:
| order_id | order_date | total_amount |
|———-|————|————–|
| 1 | 2023-10-25 | 150.00 |
| 2 | 2023-10-26 | 200.00 |
| 4 | 2023-10-28 | 250.00 |
The query filters the rows based on the condition total_amount <= 250.00 and retrieves the orders with a total amount less than or equal to 250.00.
SQL Not equal to with Example
In SQL, the not equal to operator (<> or !=) is used to compare two expressions to determine if they are not equal. It is commonly used in the WHERE clause of a SQL query to filter rows based on certain conditions. Here is an example that demonstrates the use of the not equal to operator:
Suppose we have a table named students with the following structure:
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
age INT
);
Let’s assume the students table contains the following data:
| student_id | student_name | age |
|————|————–|—–|
| 1 | John | 20 |
| 2 | Emily | 22 |
| 3 | Michael | 21 |
| 4 | Jessica | 20 |
If we want to retrieve the students who are not 20 years old, we can use the not equal to operator in the WHERE clause:
SELECT * FROM students WHERE age <> 20;
This query will return the following result:
| student_id | student_name | age |
|————|————–|—–|
| 2 | Emily | 22 |
| 3 | Michael | 21 |
The query filters the rows based on the condition age <> 20 and retrieves the students whose age is not equal to 20. Both <> and != can be used to denote the not equal to operator in SQL.