MySQL

In MySQL, the <> and != operators are used to filter rows where a column's value does not match a specified value. Both <> and != perform the same function, and you can use either depending on your preference. These operators are essential when you need to retrieve data that excludes certain values, making them helpful for filtering out specific records from the result set.

Here’s a detailed guide on using <> and != in MySQL with examples for new students:

1. Basic Syntax for <> (Not Equal):

   • The <> operator is used to return rows where a column's value is not equal to a specific value.
   • Syntax:

   SELECT * 
   FROM employees 
   WHERE department_id <> 3;

   • This query retrieves all employees who are not in department 3.

2. Using != (Not Equal):

   • The != operator functions similarly to <> and is often preferred by some users. Both operators are interchangeable.
   • Example:

   SELECT * 
   FROM employees 
   WHERE job_title != 'Manager';

   • This query retrieves employees whose job title is not "Manager".

3. Using <> or != with Numeric Comparisons:

   • You can use these operators to filter numeric values. For example, you might want to exclude employees with a specific salary.
   • Example:

   SELECT * 
   FROM employees 
   WHERE salary <> 60000;

   • This query retrieves all employees whose salary is not exactly 60,000.

4. Using <> or != with String Comparisons:

   • These operators can also be used to compare string values.
   • Example:

   SELECT * 
   FROM employees 
   WHERE last_name != 'Smith';

   • This query returns all employees whose last name is not "Smith".

5. Combining with Other Conditions:

   • You can combine the <> or != operators with AND or OR to apply multiple conditions in a query.
   • Example:

   SELECT * 
   FROM employees 
   WHERE department_id <> 3 
   AND salary != 50000;

   • This query retrieves employees who are not in department 3 and who also do not have a salary of 50,000.

6. Handling NULL Values with <> and !=:

   • It’s important to note that <> and != do not compare NULL values directly. If you need to handle NULL, use the IS NULL or IS NOT NULL operators.
   • Example:

   SELECT * 
   FROM employees 
   WHERE manager_id IS NOT NULL 
   AND manager_id <> 1;

   • This query returns employees who have a manager assigned (i.e., manager_id is not NULL) and whose manager is not employee 1.

7. Using <> or != with Subqueries:

   • You can use these operators in combination with subqueries to exclude specific values returned from another query.
   • Example:

   SELECT * 
   FROM employees 
   WHERE department_id <> (SELECT department_id FROM departments WHERE department_name = 'HR');

   • This query retrieves employees who are not in the HR department.

8. Performance Considerations:

   • Using <> and != in large datasets can sometimes impact performance, especially when used on non-indexed columns. Indexing columns can help improve query performance.
   • Example:

   SELECT * 
   FROM employees 
   WHERE job_title <> 'Engineer';

   • If job_title is indexed, this query will be more efficient.

These examples illustrate how to use <> and != effectively in MySQL to filter records that do not match a specific value. Understanding these operators is crucial for querying data that requires exclusion of certain values or conditions.

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

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

SELECT * 
FROM act_order
WHERE order_status_id != 5;

SELECT * 
FROM act_order
WHERE shipped_date != '2023-12-12';

-- Oracle WHERE shipped_date != 12/12/23';

Example 1:

Let's examine the process of extracting information from a specified table using the SQL '!=' or '<>' (not equal) operator with string data. Retrieve records for clients labeled as male while excluding those identified as female.

Example 1 - Raw data from client table

Example 1 - Query

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

Example 1 - Query data mapping

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.

Example 1 - Query Output

Example 2:

Let's explore the procedure of extracting information from a designated table using the SQL '!=' or '<>' (not equal) operator with numeric data. Fetch orders with statuses different from 5.

Example 2 - Raw data from orders table

Example 2 - Query

SELECT * FROM act_order WHERE order_status_id != 5;

Example 2 - Query data mapping

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.

Example 2 - Query Output

Example 3:

Let's delve into the process of extracting information from a specified table using the SQL '!=' or '<>' (not equal) operator with date data. Retrieve orders with shipping dates other than December 12th.

Example 3 - Raw data from orders table

Example 3 - Query

SELECT * FROM act_order WHERE shipped_date != '2023-12-12';

Example 3 - Query data mapping

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. Please be aware that the query will attempt to filter based on the value of December 12th. Since many records have NULL for the shipped date value, NULL cannot be compared as it is not an actual value. Consequently, these records will be excluded from the results as well.

Example 3 - Query Output