w3resource

MySQL NOT IN() function

NOT IN() function

MySQL NOT IN() makes sure that the expression proceeded does not have any of the values present in the arguments.

Syntax:

expr NOT IN (value,...)

MySQL Version: 5.6

Example: MySQL NOT IN() function

Sample table: book_mast

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
| BK011   | The Experimental Analysis of Cat    | 0000979011  | CA004   | AUT011 | P005   | 2007-06-09 | French   |     225 |      95.00 |
| BK012   | The Nature  of World                | 0000979012  | CA004   | AUT005 | P008   | 2005-12-20 | English  |     350 |      88.00 |
| BK013   | Environment a Sustainable Future    | 0000979013  | CA004   | AUT012 | P001   | 2003-10-27 | German   |     165 |     100.00 |
| BK014   | Concepts in Health                  | 0000979014  | CA005   | AUT013 | P004   | 2001-08-25 | NULL     |     320 |     180.00 |
| BK015   | Anatomy & Physiology                | 0000979015  | CA005   | AUT014 | P008   | 2000-10-10 | Hindi    |     225 |     135.00 |
| BK016   | Networks and Telecommunications     | 00009790_16 | CA003   | AUT015 | P003   | 2002-01-01 | French   |      95 |      45.00 |
| BK1234  | ASDFASD                             | ASDF        | ASDF    | ASDF   | P010   | 2001-10-10 | ENGLISH  |     235 |     234.00 |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
17 rows in set (0.03 sec)

Sample table:publisher

+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| pub_id | pub_name                     | pub_city  | country   | country_office | no_of_branch | estd       |
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
| P001   | Jex Max Publication          | New York  | USA       | New York       |           15 | 1969-12-25 |
| P002   | BPP Publication              | Mumbai    | India     | New Delhi      |           10 | 1985-10-01 |
| P003   | New Harrold Publication      | Adelaide  | Australia | Sydney         |            6 | 1975-09-05 |
| P004   | Ultra Press Inc.             | London    | UK        | London         |            8 | 1948-07-10 |
| P005   | Mountain Publication         | Houstan   | USA       | Sun Diego      |           25 | 1975-01-01 |
| P006   | Summer Night Publication     | New York  | USA       | Atlanta        |           10 | 1990-12-10 |
| P007   | Pieterson Grp. of Publishers | Cambridge | UK        | London         |            6 | 1950-07-15 |
| P008   | Novel Publisher Ltd.         | New Delhi | India     | Bangalore      |           10 | 2000-01-01 |
| P009   | ASDFASD                      | ASDF      | ASD       | ASDF           |            1 | 0000-00-00 |
+--------+------------------------------+-----------+-----------+----------------+--------------+------------+
9 rows in set (0.04 sec)

If you want to fetch the rows from the table book_mast which contain such books, not written in English and the price of the books are not 100 or 200, the following statement can be used.

Code:

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

Sample Output:

+----------------------------------+------------+----------+---------+------------+
| book_name                        | dt_of_pub  | pub_lang | no_page | book_price |
+----------------------------------+------------+----------+---------+------------+
| Fundamentals of Heat             | 2003-08-10 | German   |     247 |     112.00 |
| 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 |
| Anatomy & Physiology             | 2000-10-10 | Hindi    |     225 |     135.00 |
| Networks and Telecommunications  | 2002-01-01 | French   |      95 |      45.00 |
+----------------------------------+------------+----------+---------+------------+
6 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-in - php mysql examples | w3resource</title>
<meta name="description" content="example-not-in - 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 books which are not written in English as well as priced neither 100 nor 200, along with their country, city and date of establishment:</h2>
<table class='table table-bordered'>
<tr>
<th>Book</th><th>Date of publish</th><th>Language</th><th>Number of pages</th><th>Price</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 book_name,dt_of_pub,pub_lang,no_page,book_price
FROM book_mast
WHERE pub_lang!="English"
AND book_price NOT IN (100,200)') as $row) {
echo "<tr>";
echo "<td>" . $row['book_name'] . "</td>";
echo "<td>" . $row['dt_of_pub'] . "</td>";
echo "<td>" . $row['pub_lang'] . "</td>";
echo "<td>" . $row['no_page'] . "</td>";
echo "<td>" . $row['book_price'] . "</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-in</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 book_name,dt_of_pub,pub_lang,no_page,book_price FROM book_mast WHERE pub_lang!='English' AND book_price NOT IN (100,200)";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Book</td>
<td>Date of publish</td>
<td>Language</td>
<td>Number of pages</td>
<td>Price</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("book_name")%></TD>
<TD><%=rs.getString("dt_of_pub")%></TD>
<TD><%=rs.getString("pub_lang")%></TD>
<TD><%=rs.getString("no_page")%></TD>
<TD><%=rs.getString("book_price")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

Example

If you want to fetch the rows from the table book_mast which contain books not written in English or German, the following sql statement can be used.

Code:

SELECT book_name,dt_of_pub,pub_lang,no_page,book_price
FROM book_mast
WHERE pub_lang NOT IN("English","German");

Sample Output:

+----------------------------------+------------+----------+---------+------------+
| book_name                        | dt_of_pub  | pub_lang | no_page | book_price |
+----------------------------------+------------+----------+---------+------------+
| Guide to Networking              | 2002-09-10 | Hindi    |     510 |     200.00 |
| Advanced 3d Graphics             | 2004-02-16 | Hindi    |     165 |      56.00 |
| The Experimental Analysis of Cat | 2007-06-09 | French   |     225 |      95.00 |
| Anatomy & Physiology             | 2000-10-10 | Hindi    |     225 |     135.00 |
| Networks and Telecommunications  | 2002-01-01 | French   |      95 |      45.00 |
+----------------------------------+------------+----------+---------+------------+
5 rows in set (0.00 sec)

View the example in browser

Example of MySQL NOT IN using two tables

If you want to fetch those rows from the table book_mast which does not contain those pub_id's which are not exist in publisher table, the following sql can be used.

SELECT * FROM book_mast 
WHERE pub_id NOT IN(
SELECT pub_id  FROM publisher);

Sample Output:

+---------+-----------+---------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name | isbn_no | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-----------+---------+---------+--------+--------+------------+----------+---------+------------+
| BK1234  | ASDFASD   | ASDF    | ASDF    | ASDF   | P010   | 2001-10-10 | ENGLISH  |     235 |     234.00 |
+---------+-----------+---------+---------+--------+--------+------------+----------+---------+------------+
1 row in set (0.06 sec)

Slideshow of MySQL Comparison Function and Operators

MySQL Comparison Function and Operators, slide presentation

Previous: NOT EQUAL OPERATOR(<>,!=)
Next: NOT LIKE



Inviting useful, relevant, well-written and unique guest posts