SQL, or Structured Query Language, is a powerful tool used by programmers and database administrators to manage and manipulate data within relational databases.
Among its many features, SQL provides a variety of comparison operators that allow users to compare values and filter data based on specific conditions. One such operator is the SQL Not Equal operator, represented by the symbols ‘<>’ in most database management systems.
The SQL Not Equal operator is designed to compare two expressions and return a Boolean result, which is either TRUE if the expressions are not equal or FALSE if they are equal. This operator is particularly useful when filtering data that does not meet certain criteria, offering great flexibility in data analysis and manipulation.
In this comprehensive guide, we’ll demystify the SQL Not Equal operator, a vital tool for effective data analysis and manipulation. We’ll look at its basics, proper implementation in queries, interaction with various data types, and its role in more complex SQL scenarios.
By the end, you’ll have a solid understanding of how to use the Not Equal operator in SQL.
Let’s start by understanding the basics of SQL Not Equal operator!
SQL Not Equal Basics
In SQL, comparison operators, such as the Not Equal operator, form the core of data filtering from a database table. The Not Equal operator, which is the opposite of the equal comparison operator, is used to create SQL statements that return specific non-matching results.
In this section, we’ll look at the basics of the SQL Not Equal operator, explaining how it helps refine your queries effectively.
1. Comparison Operators
In SQL, comparison operators are used to compare values and create a logical expression that results in either true, false, or NULL.
One important comparison operator is the Not Equal operator, which tests if the values of expressions are not equal. It’s frequently used in WHERE clauses to filter records that do not meet specific conditions.
Some common comparison operators include:
- =: Equal to
- >: Greater than
- <: Less than
- >=: Greater than or equal to
- <=: Less than or equal to
2. Not Equal Symbols
The Not Equal operator in SQL can be represented in two different ways:
- <>: The less than symbol (<) followed by the greater than symbol (>). For example, A <> B means “A is not equal to B”
- !=: The exclamation mark (!) followed by the equal sign (=). For example, A != B means “A is not equal to B”
Both symbols perform the same function, and you can choose either one depending on your preference or requirements. It is recommended to use <>, as it is in line with the ISO standard, whereas != doesn’t comply with ISO standards.
To understand how the Not Equal operator works, let’s use the employees table from the popular Chinook sample database. The table has records for 8 employees. Let’s see some selected columns from the table:
Here’s an example of using the Not Equal operator in a SQL query:
SELECT EmployeeId, LastName, FirstName, Title, City FROM employees WHERE Title != "Sales Support Agent";
Let’s break down the different parts of the query above:
When you run the query on the Chinook’s database, here is the output:
The query returns rows from the employees table after filtering out the employees with the title “Sales Support Agent”.
Using SQL Not Equal in Queries
When working with SQL, it’s often necessary to compare values in columns using comparison operators. One common comparison is the SQL Not Equal operator. In this section, let’s see some examples of using the SQL Not Equal operator in Select Statements.
1. Select Statement
The SELECT statement is used to select data from SQL database tables and is the most crucial element of SQL queries.
For example, the following query would return the four columns from the table customers in the Chinook database:
SELECT FirstName, LastName, City, Phone FROM customers;
The result set would look like this:
2. Where Clause
If you want to use the SQL Not Equal operator within a Select Statement, you need to utilize the WHERE clause. Using WHERE, the Not Equal operator can be used to compare two expressions. The returned result set will contain rows where the specified columns do not match.
For example, suppose you want to exclude certain records from the employees table based on the “not equal to” condition. You could use a query like this:
SELECT EmployeeId, LastName, FirstName, Title, City FROM employees WHERE City <> 'Calgary'
The above query would return the following result set, in which we have successfully filtered the records where the City is not equal to ‘Calgary’.
Working with Different Data Types
When using SQL Not Equal (<>) operator, it is crucial to understand how it works with different data types. In this section, we’ll focus on understanding the data type precedence and how to work with strings and quotes.
1. Data Type Precedence
In T-SQL, expressions involved in a comparison must have implicitly convertible data types. Data type precedence determines the order in which data types are evaluated and implicitly converted when multiple data types are involved in an expression.
When you compare expressions with different data types, SQL will automatically convert them based on the data type precedence rules. It’s essential to be aware of these rules to avoid unexpected results.
2. Using Strings and Quotes
When working with strings and the Not Equal operator, proper handling of quotes is essential. String literals must be enclosed in single or double quotes depending on the SQL dialect you’re using. For example, in SQL Server, you would use single quotes for string literals:
SELECT FirstName, LastName, Phone FROM customers WHERE city <> 'Prague';
In this example, the city column is being compared to the string ‘Prague’ using the Not Equal (<>) operator. If you do not use the correct quoting, it will result in a syntax error.
Here’s the output of the above query:
Complex SQL Not Equal Scenarios
In this section, we’ll explore some complex scenarios involving the SQL Not Equal operator in the context of group by and aggregation, as well as stored procedures.
1. Combining Multiple Conditions
When working with SQL, you may need to filter results based on multiple conditions. Combining multiple conditions in SQL can be done using different operators such as AND, OR, IN, BETWEEN, and LIKE to create more complex and powerful queries.
AND and OR Operators
The AND and OR operators allow you to combine simple conditions into more complex ones. The AND operator returns true if both conditions are true, while the OR operator returns true if at least one of the conditions is true.
Here’s an example using both operators:
SELECT Name, Milliseconds / 1000.0 AS Seconds FROM tracks WHERE Composer <> 'AC/DC' AND Milliseconds / 1000.0 > 900
Let’s break down this query:
The output looks like this:
The IN operator is used to specify multiple possible values for a column, acting like multiple OR conditions. It can be used to simplify your query, making it more readable and efficient. When combined with the NOT operator, it can also be used to filter out specific values in a list.
SELECT FirstName, LastName, City, Phone FROM customers WHERE City NOT IN ('Paris', 'Berlin', 'Lisbon')
In the above query, the WHERE condition filters all records with city not equal to Paris, Berlin, and Lisbon.
Here’s the output:
BETWEEN and LIKE Operators
The BETWEEN operator is used to filter results within a specific range of values. It is inclusive of the range boundaries and can be used with both numerical and date values. Let’s use the BETWEEN operator to select all tracks in the Chinook database that are between 50 and 60 seconds long:
SELECT Name, Composer, Milliseconds / 1000.0 AS Seconds FROM tracks WHERE Milliseconds / 1000.0 BETWEEN 50 AND 60;
Let’s analyze the SQL statement above:
The output of the SQL query only returns tracks whose length is between 50 and 60 seconds. Here’s the output:
The LIKE operator is used for pattern matching in string values. It allows you to search for a specified pattern within a column. The percent sign (%) is used as a wildcard, representing any number of characters. The underscore (_) represents exactly one character.
Here’s an example using the LIKE operator to select all tracks with names starting with the word “Let”:
SELECT Name FROM tracks WHERE Name LIKE 'Let%';
The above SQL statement gives the following output:
2. Handling Null Values with SQL Not Equal
In SQL, null values act as placeholders for the absence of a value in a field. Using the Not Equal operator (<> or !=) with null values will not evaluate to true or false. Instead, it will result in an unknown state.
This section will discuss how the ANSI_NULLS setting can affect the output of these queries and will present some simpler approaches to deal with null values.
The ANSI_NULLS setting in SQL databases can impact the behavior of null values in SQL queries. When SET ANSI_NULLS is ON, which is the default, the comparison of a null value with any value, even another null, will result in an unknown state.
For example, the following query returns no results.
SET ANSI_NULLS ON; SELECT * FROM Products WHERE quantity = NULL; -- No results
If you want to change this behavior, you can turn off the ANSI_NULLS setting, which essentially makes SQL treat NULL like any other number. What this means is that a check like “Price <> NULL” will result in either True or False.
Let’s see this in action. For this example, we will use a dummy SQL table named products that stores information about the price and quantity of five products. Here is the table:
If we set ANSI_NULLS OFF, the following query will work normally:
SET ANSI_NULLS OFF; SELECT * FROM Products WHERE quantity = NULL; -- Results with non-null Prices
Here’s the output:
However, it is generally recommended to keep ANSI_NULLS ON and use the IS NULL and IS NOT NULL operators to handle null values. This adheres to the SQL standard and ensures consistent behavior across different SQL implementations.
IS NULL and IS NOT NULL Operators
IS NULL and IS NOT NULL are special operators in SQL to check if values in a column and equal to or not equal to NULL. For example, the following query returns all records with missing prices:
SELECT * FROM products WHERE price IS NULL;
And it works without setting ANSI_NULL OFF. Here’s the output:
The IS NOT NULL operator works in a similar way, but returns records with defined values. For example, see the following query:
SELECT * FROM products WHERE price IS NOT NULL;
The output will have all records for which the prices are defined.
If you are carrying out data analysis on the results of your SQL query, NULL values can lead to unexpected results. So, it’s better to replace these with a default value that can be used to differentiate these missing values from other records.
In SQL, you can also use the COALESCE function to do this. For example, the following query replaces the NULL prices with a negative number:
SELECT id, name, COALESCE(price, -100) as price, quantity FROM products;
Let’s break down what we did:
This gives the following output:
3. GROUP BY and Aggregation
When working with SQL GROUP BY clause and aggregations, the Not Equal operator may be used for filtering and comparing aggregated values. Let’s consider a sample scenario:
Suppose we want to find how many tracks each Composer has and what the total duration is for all the records by a composer.
Here’s how this can be done using the GROUP BY clause:
SELECT Composer, COUNT(Name) as 'Total Tracks', SUM(Milliseconds/1000) AS 'Duration' FROM tracks WHERE COMPOSER IS NOT NULL GROUP BY Composer;
Let’s analyze the above query:
This query gives the following output:
GROUP BY clause can also use the HAVING operator to filter records based on aggregates.
For example, the last GROUP BY query can be updated to filter out any records with a total number of tracks equal to 1.
SELECT Composer, COUNT(Name) as 'Total Tracks', SUM(Milliseconds/1000) AS 'Duration' FROM tracks WHERE Composer IS NOT NULL GROUP BY Composer HAVING "Total tracks" <> 1;
The output shows that all composers with more than one record:
4. Stored Procedures
Stored procedures in SQL databases enable us to encapsulate complex logic and reuse it across the application. In this context, the Not Equal operator can be applied to control flow and filtration within the stored procedures.
Let’s create a simple stored procedure that returns sales data for a specific region, excluding certain years using T-SQL for SQL Server.
Here’s the dummy sales_data table that we will use for this example.
Here’s the stored procedure named GetSalesData:
CREATE PROCEDURE GetSalesData @region VARCHAR(50), @excludeYear1 INT, @excludeYear2 INT AS BEGIN SELECT year, sales FROM sales_data WHERE region = @region AND year <> @excludeYear1 AND year <> @excludeYear2; END;
This stored procedure accepts three parameters – region, and two years to be excluded. It then filters the required sales data with the Not Equal operator in the condition.
To execute the stored procedure, use the following statement:
EXEC GetSalesData 'North America', 2019, 2020;
The output shows only the records where the region is “North America” and the year is not 2019 and 2020.
Mastering the SQL Not Equal operator is a crucial step toward creating powerful and efficient SQL statements. This operator allows us to filter and retrieve data that doesn’t match specific conditions, paving the way for more flexible and targeted data analysis.
Throughout this guide, we delved into the comparison operators, learned about the symbols representing Not Equal, and examined how to employ this operator in various types of SQL queries. We also tackled more complex scenarios, blending ‘Not Equal’ with multiple conditions, handling NULL values, and combining it with AND, OR, IN, Between, LIKE operators, as well as with group by and aggregation functions.
Remember, the power of SQL lies in its simplicity and versatility. The Not Equal operator, though simple at first glance, offers a wealth of opportunities for refining your database queries.
Experiment with it, apply it in various scenarios and don’t shy away from exploring its potential in depth. This will not only help you work better with SQL databases but also with analytics tools like Power BI.
Frequently Asked Questions
How do I filter results using the NOT EQUAL operator in SQL?
To filter results using the NOT EQUAL operator in SQL, use the <> or != symbols in your WHERE clause. For example:
SELECT column1, column2 FROM table_name WHERE column_name <> 'value';
This query will return the rows where the specified column’s value is not equal to the given value.
Let’s see an example to select records from the sales_data table with region not equal to “Asia”.
SELECT * FROM sales_data WHERE region <> 'Asia';
The following table is the output:
What are the differences between != and <> operators in SQL?
The != and <> are two different notations for expressing the NOT EQUAL operator in SQL. Both can be used interchangeably, but <> is aligned with the ANSI SQL standard, making it more portable and compatible across different database systems.
How to use NOT NULL with NOT EQUAL operators in a query?
To use NOT NULL with NOT EQUAL operators in a query, combine the NOT NULL and NOT EQUAL conditions in your WHERE clause with the AND keyword as follows:
SELECT column1, column2 FROM table_name WHERE column_name IS NOT NULL AND column_name <> 'value';
This query filters out rows where the specified column is not equal to the given value and is not null.
Here’s an example using the dummy products table:
SELECT id, name, price FROM products WHERE price IS NOT NULL AND quantity <>10;
This query will first filter out all records with missing prices and quantity equal to 10 and display only the id, name, and price columns in the output.
What is the syntax for NOT EQUAL in an Oracle query?
In Oracle, the NOT EQUAL syntax is the same as in other SQL databases, you can use either <> or !=:
SELECT column1, column2 FROM table_name WHERE column_name <> 'value';
Are there any alternatives to NOT EQUAL when filtering records in SQL?
Instead of using NOT EQUAL, you can filter records using the NOT IN operator to exclude specific values. For example:
SELECT * FROM sales_data WHERE region NOT IN ('Asia', 'Europe');
This query will return rows where the specified column’s value is not equal to any of the values specified in the IN clause. For the above example, the output will be:
You can also use the NOT BETWEEN operator. This selects values outside of a range.
For example, to select records from the table sales_data where the year is not between 2019 and 2020, you can use the following SQL statement:
SELECT * FROM sales_data WHERE year NOT BETWEEN 2019 AND 2020;
This gives only the records where the year is outside the range from 2019 to 2020.