MySQL not equal 

Introduction to MySQL Not Equal

MySQL Not Equal is used to filter the rows that are ‘NOT Equal to’ the specified ‘value’. The symbol which represents the ‘NOT Equal to’ is ‘<>’ or ‘!=’. The mentioned symbols are used to utilize the operations of operators. Simple operations of the ‘Not Equal’ operator is, we specify the expression and the rows that satisfy will be displayed, and the rest will be omitted. For example, if you are planning to exclude values such as salary = 35000. We specify it as a salary <> 35000.

In this session, we will learn more about the ‘Not Equal’ operator, how it works, along with examples: –

Syntax

Below is the syntax for the ‘Not equal’ operator.

<select statement>
< where clause>
<column><> ‘value’;
[or] <select statement>
< where clause>
<column> !=  ‘value’;

How does MySQL Not Equal works?

Now let us create a table, apply the ‘not equal’ operator, and check how it works.

create table Not_equal_demo
(
id int,
name varchar(20),
amount int
);

Insert the below rows into the above table: –

insert into Not_equal_demo values ( 1, 'Rose', 90000 );
insert into Not_equal_demo values ( 2, 'Sam', 89000 );
insert into Not_equal_demo values ( 3, 'Will', 79000 );
insert into Not_equal_demo values ( 4, 'Henry', 56000 );
insert into Not_equal_demo values ( 5, 'Pinky', 35000 );
insert into Not_equal_demo values ( 6, 'Robert', 35000 );

Let us select the data from the above table: –

Select * from Not_equal_demo;

Screenshot for the same: –

Consider another table as below: –

create table job_status
(
id int,
status varchar(15)
);

Insert data into the table as below: –

insert into job_status values ( 1, 'yes');
insert into job_status values ( 2, 'yes');
insert into job_status values ( 3, 'no');
insert into job_status values ( 4, 'no');
insert into job_status values ( 5, 'yes');
insert into job_status values ( 6, 'yes');

Now let us select data from the above table: –

Select * from job_status;

Screenshot for the same: –

Now let us filter the data from the above table. But, first, let us get the data from the table where the amount is not equal to 35000.

SELECT * FROM NOT_EQUAL_DEMO WHERE AMOUNT <> 35000;

Here in the above query, we filter the data and extract all rows that are not equal to 35000.

[OR]

SELECT * FROM NOT_EQUAL_DEMO WHERE AMOUNT != 35000;

Both the symbols act similar and get the data from the table. So the screenshot is for the same.

Using not equal in a subquery : –

Select * from not_equal_demo
where id not in( select id from job_status where status != 'yes') ;

Here let us see the output of the inner query first. Below is for the same.

select id from job_status where status != 'yes';

Screenshot for the same: –

Here the output from the inner query is 3,4. This output will be passed to the outer query, and the outer query gets the id row data not equal to 3,4. Which means we get the output as for id = 1, 2, 5, 6.

Select * from not_equal_demo
where id NOT IN( select id from job_status where status != 'yes') ;

Screenshot for the same: –

Example of MySQL not equal

Now let us consider another table as below and find the working of ‘Not Equal’.

CREATE TABLE test_not_equal
(
SERIAL_NO INT
, NAME VARCHAR(20)
, LOCATION VARCHAR(20)
, AGE INT
, OCCUPATION VARCHAR(20)
, PHONE_NO VARCHAR(10)
);

Now let us insert data into the table: –

INSERT INTO TEST_NOT_EQUAL VALUES (1, 'Rose', 'USA', 24, 'Software Engineer', '9876545676' );
INSERT INTO TEST_NOT_EQUAL VALUES (2, 'Rahul', 'India', NULL , 'Artist', '8765678432' );
INSERT INTO TEST_NOT_EQUAL VALUES (3, 'Will', 'Denmark', 24, 'Software Engineer', '7656789843' );
INSERT INTO TEST_NOT_EQUAL VALUES (4, 'Ben', 'Polland', NULL , 'Actress', '9123456435' );
INSERT INTO TEST_NOT_EQUAL VALUES (5, 'Sian', 'FIA', 24, 'Writer', '8453672456' );
INSERT INTO TEST_NOT_EQUAL VALUES (6, 'Rodger', 'Norway', 24, 'Software Engineer', '7893412564' );
INSERT INTO TEST_NOT_EQUAL VALUES (7, 'Harry', 'USA', NULL, 'Artist', '9237645128' );
INSERT INTO TEST_NOT_EQUAL VALUES (8, 'Kiyana', 'USA', 24, 'Software Engineer', '7453478562' );
INSERT INTO TEST_NOT_EQUAL VALUES (9, 'Pradhush', 'USA', 24, 'Writer', '7554637789' );
INSERT INTO TEST_NOT_EQUAL VALUES (10, 'Dawson', 'USA', NULL, 'Painter', '9996665558' );

Now let us see the data from the table: –

Select * from test_not_equal;

Screenshot for the same: –

Now let us get the data from the table where Location is not equal to ‘USA’.

SELECT * FROM TEST_NOT_EQUAL WHERE LOCATION <> 'USA';

Screenshot for the same: –

You could see that the output has filtered the ‘USA’ data and returned the rest of the data in the above out.

We can perform the Sub query for the same table and apply the ‘not equal’.

SELECT * FROM TEST_NOT_EQUAL
WHERE SERIAL_NO IN ( SELECT SERIAL_NO FROM TEST_NOT_EQUAL WHERE
LOCATION <> 'USA');

The output of the inner query is as below, which will get the data from the table where location is not equal to ‘USA’.

SELECT SERIAL_NO FROM TEST_NOT_EQUAL WHERE LOCATION <> 'USA';

Screenshot for the same: –

Here based on the output of the inner query. We have used the “IN” operator, which will retrieve the data that is “equal to” the inner output.

SELECT * FROM TEST_NOT_EQUAL
WHERE SERIAL_NO IN( SELECT SERIAL_NO FROM TEST_NOT_EQUAL WHERE LOCATION <>'USA');
[or] SELECT * FROM TEST_NOT_EQUAL
WHERE SERIAL_NO IN( SELECT SERIAL_NO FROM TEST_NOT_EQUAL WHERE LOCATION !='USA');

Below is the output for the same: –

Conclusion

MySQL Not Equal is used to filter the rows that are ‘NOT Equal to’ the specified ‘value’. The symbol which represents the ‘NOT Equal to’ is ‘<>’ or ‘!=’. The mentioned symbols are used to utilize the operations of operators. Simple operations of the ‘Not Equal’ operator is, we specify the expression and the rows that satisfy will be displayed and rest will be omitted.

Recommended Articles

This is a guide to MySQL not equal . Here we discuss How does MySQL Not Equal works and Examples along with the outputs. You may also have a look at the following articles to learn more –

1. MySQL BIT
2. MySQL DDL
3. MySQL CURDATE
4. MySQL SHOW