MySQL

In MySQL, comparison operators are used to compare two values and return a boolean result (either TRUE or FALSE). They are commonly used in the WHERE clause to filter rows based on specific conditions, but they can also be used in SELECT, ORDER BY, and other parts of a query. These operators allow you to check for equality, inequality, and other relationships between values, such as greater than, less than, or within a range.

Here’s a detailed explanation of the most common comparison operators in MySQL, along with examples:

1. Equal to (=):

   • The equal operator (=) is used to compare if two values are the same.
   • Syntax:

   SELECT first_name, last_name 
   FROM employees 
   WHERE department_id = 2;

   • This query retrieves employees who belong to department 2.

2. Not equal to (<> or !=):

   • The not equal operators (<> or !=) are used to compare if two values are different.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE department_id <> 3;

   • This query returns employees who do not belong to department 3.

3. Greater than (>):

   • The greater than operator (>) is used to check if the left operand is greater than the right operand.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE salary > 50000;

   • This query retrieves employees with a salary greater than 50,000.

4. Less than (<):

   • The less than operator (<) is used to check if the left operand is less than the right operand.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE salary < 30000;

   • This query retrieves employees whose salary is less than 30,000.

5. Greater than or equal to (>=):

   • The greater than or equal to operator (>=) checks if the left operand is greater than or equal to the right operand.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE salary >= 60000;

   • This query retrieves employees who have a salary of 60,000 or higher.

6. Less than or equal to (<=):

   • The less than or equal to operator (<=) checks if the left operand is less than or equal to the right operand.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE salary <= 40000;

   • This query retrieves employees who earn 40,000 or less.

7. BETWEEN (for range checks):

   • The BETWEEN operator is used to check if a value falls within a specified range.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE salary BETWEEN 30000 AND 50000;

   • This query retrieves employees whose salary is between 30,000 and 50,000, inclusive.

8. IN (for checking multiple values):

   • The IN operator is used to check if a value matches any value in a list of values.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE department_id IN (1, 2, 3);

   • This query retrieves employees who belong to department 1, 2, or 3.

9. IS NULL (for checking NULL values):

   • The IS NULL operator is used to check if a value is NULL.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE manager_id IS NULL;

   • This query retrieves employees who do not have a manager assigned (i.e., manager_id is NULL).

10. IS NOT NULL (for checking non-NULL values):

    • The IS NOT NULL operator is used to check if a value is not NULL.
    • Example:

    SELECT first_name, last_name 
    FROM employees 
    WHERE manager_id IS NOT NULL;

    • This query retrieves employees who have a manager assigned.

11. LIKE (for pattern matching):

    • The LIKE operator is used for pattern matching in strings. Use % as a wildcard for multiple characters and _ as a wildcard for a single character.
    • Example:

    SELECT first_name, last_name 
    FROM employees 
    WHERE first_name LIKE 'J%';

    • This query retrieves employees whose first name starts with the letter 'J'.

12. NOT LIKE:

    • The NOT LIKE operator is used to filter out rows that match a specific pattern.
    • Example:

    SELECT first_name, last_name 
    FROM employees 
    WHERE first_name NOT LIKE 'A%';

    • This query retrieves employees whose first name does not start with 'A'.

Comparison operators in MySQL provide the ability to filter, sort, and compare data efficiently in a wide range of scenarios. Using these operators effectively allows for more precise and complex queries.

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 NUL

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.