Not Equal in SQL

Introduction

Think of a situation when you want to access the data rows from the database table except the few ones,

Here the not equal operator comes into the picture. It is a comparison operator of SQL and is used to check whether two expressions that are written on both the sides of the operator are the same or not. If they are not equal, the condition will evaluate to be true, and the given query will execute on the corresponding data set.

The query which we were discussing in the previous paragraph could be from the category of Data Query Language, which means the query execution will fetch some data from the table or Data Manipulation Language which means the query execution will change some data on the database.

Suffice to say, some sort of conditional data manipulation and access is achieved through the involvement of this operator along with the statements of SQL query.

Syntax of Not Equal in SQL

The Not Equal in SQL is used along with the WHERE Clause in the SQL Statements to identify the records to be selected. The Not Equal operator is also called as an inequality operator. It is represented by <> or !=.

The part of SQL query which contains the not equal operator is shown below,

WHERE expression1 <> expression2

• The expression1 and expression2 could be any number, text, constant value, or column value accessed through the select statement.

• The default strings comparison in SQL is case insensitive which means the statement 'ABC' <> 'abc' will evaluate to false.

• If expression1 and expression2 are of different types, i.e., strings and numbers, they are internally type casted for comparison. The both statements 1 <> '1'; and '1' <> 1; will evaluate to false.

Examples of Not Equal in SQL

Here we are going to take some random data and we will use the Not Equal operator to work on that data and consequently produce some useful insights. First of all, let’s create some data which will be used in these examples.

Create Database

Initially, we are going to create a database and switch to that one with the help of USE statement.

Create Table

Once our database is ready, we can create a table, the query written below will create a table named engineering_student which will have the fields; id as primary key, name, branch, grade, and backlogs. The NOT NULL written along with the field variable indicates that while inserting data these values must be provided. The int and varchar are the data types of the field, also the varchar(30) denotes that a column or field can have a value of maximum of 30 characters.

Output: Something like this will be shown to you on successful table creation.

Insert Some Data

Now our database consists of an empty table and we can insert the data. Here we are going to insert some random data for example purposes. The INSERT INTO statement of SQL inserts the data to the table written in the statement, along with we write the fields inside the parenthesis followed by the values for those fields.

Output:

After successful insertion we can select all rows from the table to produce the output below, We are showing it here for better understanding.

So till now, we have our data ready now, we can proceed to the examples section. The further article consists of a few examples based on the not equal operator. In these examples, we are going to use the table which we have created in the previous step.

1. Use of Inequality Operator

In this example, we want to select all students of the table whose backlog is not 1. We can directly use the not equal operator along with the WHERE Clause by providing two operands on both sides. As an outcome of the evaluation, it will filter out desired data rows.

The query written below will filter out all of the students whose backlog is equal to 1 and SELECT statement will select all remaining rows.

Output:

2. Use the Inequality Operator with Text Values

In this example, we want to select all students of the table whose branch is not CSE. The not equal operator works on the numbers as well as on the text values.

The query written below will find all students except those from the "CSE" branch.

Output:

3. Not Equal in SQL with Group By clause

In this example, we want to count and show the number of students of each branch except the EE. Here we will use the group by clause along with the not equal operator. The group by clause in SQL is used to group the rows based on the same column value. For Example- Group and find the total number of students of each branch.

The query written below will show the count of students in each branch because of the group by clause, and the "EE" branch will be excluded because of the not equal operator.

Output:

4. Not Equal in SQL with Multiple Conditions

In this example, we will combine the two conditions in the query, and we are going to select all students on the table whose grade is either A or B though at the same time, student must not be from the EE branch. We can combine the numerous conditions along with the not equal operator, i.e., AND, OR, etc, as per requirements.

The query given below will select each row of the engineering_student table where the branch is not EE and the grade is either A or B.

Output:

Difference Between (< >) and (! =) Operator

Both are used for comparisons and both will produce the same result there is no technical difference in them. But != is not an ISO standard of SQL whereas <> is according to a major revision of SQL standard which is SQL-92 We will suggest you use <> because it is ANSI SQL compatible means it will work everywhere, Though there could be a probability that some servers do not recognize the != operator. Also, there are some server which uses the != operator as an alias to <> operator, according to official docs of PostgreSQL "The != operator is converted to <> in the parser stage".