w3resource logo


MySQL not equal to operator

MySQL not equal to (<>, !=) operator

Secondary Nav

Description

MySQL Not equal is used to return a set of rows (from a table) after making sure that two expressions placed on either sides of the NOT EQUAL TO (<>) operator are not equal.

Syntax

<>, !=

MySQL Version : 5.6

Example : MySQL not equal to (<>) operator

The following MySQL statement will fetch the rows from the table publisher which contain publishers those who don't belong to the country USA.

SELECT pub_name,country,pub_city,estd 
FROM publisher 
WHERE country <>"USA";

Sample table : publisher

Output

example mysql not equal to

mysql> SELECT pub_name,country,pub_city,estd 
    -> FROM publisher 
    -> WHERE country <>"USA";
+------------------------------+-----------+-----------+------------+
| pub_name                     | country   | pub_city  | estd       |
+------------------------------+-----------+-----------+------------+
| BPP Publication              | India     | Mumbai    | 1985-10-01 | 
| New Harrold Publication      | Australia | Adelaide  | 1975-09-05 | 
| Ultra Press Inc.             | UK        | London    | 1948-07-10 | 
| Pieterson Grp. of Publishers | UK        | Cambridge | 1950-07-15 | 
| Novel Publisher Ltd.         | India     | New Delhi | 2000-01-01 | 
+------------------------------+-----------+-----------+------------+
5 rows in set (0.00 sec)

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-not-equal-operator - php mysql examples | w3resource</title>
<meta name="description" content="example-not-equal-operator - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of publishers those who don't belong to USA, along with their country, city and date of establishment:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher</th><th>Country</th><th>City</th><th>Date of establishment</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_name,country,pub_city,estd
FROM publisher
WHERE country <>"USA"') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['pub_city'] . "</td>";
echo "<td>" . $row['estd'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

JSP script

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-not-equal-operator</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT pub_name,country,pub_city,estd FROM publisher WHERE country <>'USA'";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Publisher</td>
<td>Country</td>
<td>City</td>
<td>Date of establishment</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("pub_name")%></TD>
<TD><%=rs.getString("country")%></TD>
<TD><%=rs.getString("pub_city")%></TD>
<TD><%=rs.getString("estd")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

Example : MySQL not equal to (!=) operator with AND using IN operator

The following MySQL statement will fetch the rows from the table book_mast which contain books not written in English and the price of the books are 100 or 200.

SELECT book_name,dt_of_pub,pub_lang,no_page,book_price
FROM book_mast
WHERE pub_lang!="English" AND book_price IN(100,200);

Sample table : book_mast

Output

mysql> SELECT book_name,dt_of_pub,pub_lang,no_page,book_price
    -> FROM book_mast
    -> WHERE pub_lang!="English" AND book_price IN(100,200);
+----------------------------------+------------+----------+---------+------------+
| book_name                        | dt_of_pub  | pub_lang | no_page | book_price |
+----------------------------------+------------+----------+---------+------------+
| Guide to Networking              | 2002-09-10 | Hindi    |     510 |     200.00 | 
| Environment a Sustainable Future | 2003-10-27 | German   |     165 |     100.00 | 
+----------------------------------+------------+----------+---------+------------+
2 rows in set (0.00 sec)

Example : MySQL not equal to ( !=) operator

This following MySQL statement will fetch the rows from the table book_mast which contain books not written in English and the price of the books are less than 100 or more than 200.

SELECT book_name,dt_of_pub,pub_lang,no_page,book_price
FROM book_mast
WHERE pub_lang!="English" AND book_price NOT BETWEEN 100 AND 200; 

Sample table : book_mast

Output

mysql> SELECT book_name,dt_of_pub,pub_lang,no_page,book_price
    -> FROM book_mast
    -> WHERE pub_lang!="English" AND book_price NOT BETWEEN 100 AND 200;
+----------------------------------+------------+----------+---------+------------+
| book_name                        | dt_of_pub  | pub_lang | no_page | book_price |
+----------------------------------+------------+----------+---------+------------+
| Advanced 3d Graphics             | 2004-02-16 | Hindi    |     165 |      56.00 | 
| Human Anatomy                    | 2001-05-17 | German   |      88 |      50.50 | 
| The Experimental Analysis of Cat | 2007-06-09 | French   |     225 |      95.00 | 
| Networks and Telecommunications  | 2002-01-01 | French   |      95 |      45.00 | 
+----------------------------------+------------+----------+---------+------------+
4 rows in set (0.00 sec)

Slideshow of MySQL Comparison Function and Operators

MySQL Comparison Function and Operators, slide presentation



Is this content useful for you?

 


You might be using Adblocker. Since w3resource does not charge anything from users, all of our expenses (e.g. Hosting, Content Creation, Development etc.) are met by advertisement. Please whitelist w3resource.