Updated June 2, 2023
Introduction to MySQL Not Equal
MySQL Not Equal filters the rows that are ‘NOT Equal to’ the specified ‘value’. The ‘NOT Equal to’ symbol 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 plan 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 and how it works, along with examples: –
Syntax
Below is the syntax for the ‘Not equal’ operator.
<select statement>
<where clause>
<column><> 'value';<select statement>
<where clause>
<column> != 'value';How does MySQL Not Equal work?
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 similarly 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.
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, which gets the id row data not equal to 3,4. This means we get the output as 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 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)
);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 can see that the output has filtered the ‘USA’ data and returned the rest 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 below, which will get the data from the table where the location is not equal to ‘USA’.
SELECT SERIAL_NO FROM TEST_NOT_EQUAL WHERE LOCATION <> 'USA';Screenshot for the same: –
It is here based on the output of the inner query. We have used the “IN” operator to retrieve the data “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 filters the rows that are ‘NOT Equal to’ the specified ‘value’. The ‘NOT Equal to’ symbol is ‘<>’ or ‘!=’. We use the mentioned symbols to utilize the operations of operators. The ‘Not Equal’ operator is used to specify an expression, and only the rows that satisfy the expression will be displayed, while the rest will be omitted.
Recommended Articles
We hope that this EDUCBA information on “MySQL not equal” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
