验证列是否具有空值 [英] Validate if a column has a null value

问题描述

哪个 SQL 可以更快地验证特定列是否具有空值，为什么?

Which SQL would be faster to validate if a particular column has a null value or not, why?

1) SELECT * FROM TABLE1 WHERE COL1 为 NULL

1) SELECT * FROM TABLE1 WHERE COL1 IS NULL

执行此查询，然后检查您是否能够读取任何记录.如果是，则为空值.

Execute this query and then check if you are able to read any records. If yes there are null values.

2) SELECT COUNT(COL1) FROM TABLE1 where COL1 is NULL

2) SELECT COUNT(COL1) FROM TABLE1 WHERE COL1 IS NULL

读取返回的计数，判断是否有空记录

Read the count which is returned to determine if there are any null records

使用 Oracle10g 和 SQLServer2005.

Working with Oracle10g and SQLServer2005.

推荐答案

Count(columnName) will NEVER count NULL values, count 在指定列名时跳过 NULLS 并在使用 * 时计算 NULLS

Count(columnName) will NEVER count NULL values, count skips NULLS when you specify a column name and does count NULLS when you use *

运行这个

CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)

SELECT count(*) FROM testnulls WHERE ID IS NULL --1

SELECT count(ID) FROM testnulls WHERE ID IS NULL --0

我会使用exists，因为它是一个布尔运算，并且会在第一次出现NULL时停止

I would use exists instead since it is a boolean operation and will stop at the first occurance of NULL

IF EXISTS (SELECT 1 FROM testnulls WHERE ID IS NULL)
PRINT 'YES'
ELSE
PRINT 'NO'

这篇关于验证列是否具有空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！