Khóa học miễn phí MySQL – NOT EQUAL Operator nhận dự án làm có lương

MySQL – NOT EQUAL Operator

Table of content


MySQL NOT EQUAL Operator

The MySQL NOT EQUAL operator is used to compare two values and return true if they are not equal. It is represented by “<>” and “!=”. The difference between these two is that <> follows the ISO standard, but != doesn”t. So, it is recommended to use the <> operator.

We can use this operator in WHERE clauses to filter records based on a specific condition and in GROUP BY clauses to group results.

Note: The comparison is case-sensitive by default when using this operator with text values.

Syntax

Following is the syntax of the NOT EQUAL operator in MySQL −

SELECT column1, column2, ... 
FROM table_name 
WHERE column_name <> value;

Example

Firstly, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

The below query uses INSERT INTO statement to add 7 records into above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to retrieve all the records present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

NOT EQUAL with String Values

In MySQL, we can also use the NOT EQUAL to compare two string values. It returns true if both values are not equal. We can use “<>” or “!=” in the WHERE clause of a SQL statement and exclude rows that match a specific value.

Example

In the following query, we are selecting all the records from the CUSTOMERS table whose NAME is not “Khilan”.

SELECT * FROM CUSTOMERS WHERE NAME <> "Khilan";

Output

The output of the above code is as shown below −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
3 Kaushik 23 Kota 2000.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

NOT EQUAL with GROUP BY Clause

MySQL”s NOT EQUAL operator can be used along with the GROUP BY clause. It will group the results by the values that are not equal to the specified text value.

The aggregate functions such as COUNT(), MAX(), MIN(), SUM(), and AVG() are frequently used with the GROUP BY statement.

Example

In this query, we are counting the number of records with distinct ”ID” values for each ”AGE” in the ”CUSTOMERS” table. We are excluding records where ”AGE” is equal to ”22”, and grouping the results based on the ”AGE” column.

SELECT COUNT(ID), AGE FROM CUSTOMERS 
WHERE AGE  ''22'' GROUP BY AGE;

Output

COUNT(ID) AGE
1 32
2 25
1 23
1 27
1 24

NOT EQUAL with Multiple Conditions

Depending on the situation, the NOT EQUAL operator can be used with multiple conditions in a WHERE clause to filter out rows that match specific criteria.

Example

Here, we are going to select all the customers whose salary is either “>2000” or “=2000”. At the same time, the customer must not be from “Bhopal”.

SELECT * FROM CUSTOMERS 
WHERE ADDRESS  ''Bhopal'' AND (SALARY>''2000'' OR SALARY=''2000'');

Output

When we execute the query above, the output is obtained as follows −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Negating a Condition Using NOT EQUAL

The MySQL NOT EQUAL operator can be combined with the NOT operator to negate a condition and filter out rows that meet a specific condition.

Example

The following query retrieves all rows from the “CUSTOMERS” table where the “SALARY” is equal to ”2000” −

SELECT * FROM CUSTOMERS
WHERE NOT SALARY != ''2000

Output

When the query gets executed it will generate the following output as shown below −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
3 Kaushik 23 Kota 2000.00

NOT EQUAL Operator Using a Client Program

Besides using MySQL queries to perform the NOT EQUAL operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

To perform the NOT EQUAL Operator on a MySQL table through a PHP program, we need to execute SELECT statement with NOT EQUAL Operator using the mysqli function query() as follows −

$sql = "SELECT column1, column2, ... FROM table_name 
WHERE column_name  value";
$mysqli->query($sql);

To perform the NOT EQUAL Operator on a MySQL table through a Node.js program, we need to execute SELECT statement with NOT EQUAL Operator using the query() function of the mysql2 library as follows −

sql= "SELECT column1, column2, ... FROM table_name 
WHERE column_name  value";
con.query(sql);

To perform the NOT EQUAL Operator on a MySQL table through a Java program, we need to execute SELECT statement with NOT EQUAL Operator using the JDBC function executeUpdate() as follows −

String sql = "SELECT column1, column2, ... FROM table_name 
WHERE column_name  value";
statement.executeQuery(sql);

To perform the NOT EQUAL Operator on a MySQL table through a Python program, we need to execute SELECT statement with NOT EQUAL Operator using the execute() function of the MySQL Connector/Python as follows −

not_equal_query = "SELECT column1, column2, ... FROM table_name 
WHERE column_name  value"
cursorObj.execute(not_equal_query);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error); exit(); } //printf(''Connected successfully.
''); $sql = "SELECT * FROM CUSTOMERS WHERE NAME ''Muffy''"; $result = $mysqli->query($sql); if ($result->num_rows > 0) { printf("Table records: n"); while($row = $result->fetch_assoc()) { printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f", $row["ID"], $row["NAME"], $row["AGE"], $row["ADDRESS"], $row["SALARY"]); printf("n"); } } else { printf(''No record found.
''); } mysqli_free_result($result); $mysqli->close();

Output

The output obtained is as follows −

Table records:
Id 1, Name: Ramesh, Age: 32, Address Hyderabad, Salary 4000.000000
Id 2, Name: Khilan, Age: 25, Address Kerala, Salary 8000.000000
Id 3, Name: kaushik, Age: 23, Address Hyderabad, Salary 11000.000000
Id 4, Name: Chaital, Age: 25, Address Mumbai, Salary 1200.000000
Id 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 10000.000000
Id 6, Name: Komal, Age: 29, Address Vishakapatnam, Salary 7000.000000   
var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating CUSTOMERS table
  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,''Ramesh'', 32, ''Hyderabad'',4000.00),(2,''Khilan'', 25, ''Kerala'', 8000.00),(3,''kaushik'', 23, ''Hyderabad'', 11000.00),(4,''Chaital'', 25, ''Mumbai'', 1200.00),(5,''Hardik'', 27, ''Vishakapatnam'', 10000.00),(6, ''Komal'',29, ''Vishakapatnam'', 7000.00),(7, ''Muffy'',24, ''Delhi'', 10000.00);"
  con.query(sql);

  //Using NOT EQUAL Operator
  sql = "SELECT * FROM CUSTOMERS WHERE NAME  ''Muffy"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});  

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Hyderabad'',
    SALARY: ''4000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Kerala'',
    SALARY: ''8000.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Hyderabad'',
    SALARY: ''11000.00''
  },
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''1200.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''10000.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: 29,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''7000.00''
  }
]         
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NotEqualOperator {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM CUSTOMERS WHERE NAME  ''Muffy''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String age = rs.getString("Age");
              String address = rs.getString("Address");
              String salary = rs.getString("Salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                                  

Output

The output obtained is as shown below −

Table records: 
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 30, Addresss: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 30, Addresss: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Addresss: MP, Salary: 4500.00              
import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
not_equal_query = f"""
SELECT * FROM CUSTOMERS WHERE NAME  ''Muffy
"""
cursorObj.execute(not_equal_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                               

Output

Following is the output of the above code −

(1, ''Ramesh'', 32, ''Hyderabad'', Decimal(''4000.00''))
(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(3, ''kaushik'', 23, ''Hyderabad'', Decimal(''11000.00''))
(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.00''))    

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc