w3resource
MYSQL Tutorial

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



New Content: Composer: Dependency manager for PHP, R Programming


bedava porno