SQL NULL是用于表示缺失值的术语.表中的NULL值是字段中显示为空白的值.每当我们尝试给出一个将字段或列值与NULL进行比较的条件时,它就无法正常工作.
我们可以通过使用这三个来处理NULL值.
IS NULL : 如果列值为NULL,则运算符返回true.
IS NOT NULL : 如果列值为NOT NULL,则运算符返回true.
< => : 运算符比较值(与=运算符不同)即使对于两个NULL值也是如此.
查找为NULL的列或NOT NULL,分别使用IS NULL或IS NOT NULL.
让我们考虑一个有表 tcount_tbl的示例包含两列,author和tutorial_count.我们可以为tutorial_count提供NULL值,表明作者甚至没有发布一个教程.因此,该相应作者的tutorial_count值为NULL.
执行以下查询.
create table tcount_tbl(author varchar(40) NOT NULL, tutorial_count INT); INSERT INTO tcount_tbl values ('Abdul S', 20); INSERT INTO tcount_tbl values ('Ajith kumar', 5); INSERT INTO tcount_tbl values ('Jen', NULL); INSERT INTO tcount_tbl values ('Bavya kanna', 8); INSERT INTO tcount_tbl values ('mahran', NULL); INSERT INTO tcount_tbl values ('John Poul', 10); INSERT INTO tcount_tbl values ('Sathya Murthi', 6);
使用以下命令显示 tcount_tbl 表中的所有记录.
select * from tcount_tbl;
执行上述命令后,您将收到以下输出.
+-----------------+----------------+ | author | tutorial_count | +-----------------+----------------+ | Abdul S | 20 | | Ajith kumar | 5 | | Jen | NULL | | Bavya kanna | 8 | | mahran | NULL | | John Poul | 10 | | Sathya Murthi | 6 | +-----------------+----------------+
要查找tutorial_count列为空的记录,以下是查询.
SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL;
执行查询后,您将收到以下输出.
+-----------------+----------------+ | author | tutorial_count | +-----------------+----------------+ | Jen | NULL | | mahran | NULL | +-----------------+----------------+
要查找tutorial_count列IS NOT NULL的记录,以下是查询.
SELECT * FROM tcount_tbl WHERE tutorial_count IS NOT NULL;
执行查询后,您将收到以下输出.
+-----------------+----------------+ | author | tutorial_count | +-----------------+----------------+ | Abdul S | 20 | | Ajith kumar | 5 | | Bavya kanna | 8 | | John Poul | 10 | | Sathya Murthi | 6 | +-----------------+----------------+
这是一个JDBC程序,它从表tcount_tbl中分别检索记录,其中tutorial_ count为NULL且tutorial_count为NOT NULL.将以下程序保存到 NullValues.java .
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class NullValues { public static void main(String[] args) { Connection con = null; Statement stmt_is_null = null; Statement stmt_is_not_null = null; ResultSet result = null; try { Class.forName("org.hsqldb.jdbc.JDBCDriver"); con = DriverManager.getConnection( "jdbc:hsqldb:hsql://localhost/testdb", "SA", ""); stmt_is_null = con.createStatement(); stmt_is_not_null = con.createStatement(); result = stmt_is_null.executeQuery( "SELECT * FROM tcount_tbl WHERE tutorial_count IS NULL;"); System.out.println("Records where the tutorial_count is NULL"); while(result.next()){ System.out.println(result.getString("author")+" | "+result.getInt("tutorial_count")); } result = stmt_is_not_null.executeQuery( "SELECT * FROM tcount_tbl WHERE tutorial_count IS NOT NULL;"); System.out.println("Records where the tutorial_count is NOT NULL"); while(result.next()){ System.out.println(result.getString("author")+" | "+result.getInt("tutorial_count")); } } catch (Exception e) { e.printStackTrace(System.out); } } }
使用以下命令编译并执行上述程序.
\>javac NullValues.java \>Java NullValues
执行上述命令后,您将收到以下输出.
Records where the tutorial_count is NULL Jen | 0 mahran | 0 Records where the tutorial_count is NOT NULL Abdul S | 20 Ajith kumar | 5 Bavya kanna | 8 John Poul | 10 Sathya Murthi | 6