为什么SQL不支持“= null"?而不是“为空"? [英] Why doesn't SQL support "= null" instead of "is null"?

问题描述

我不是在问是否.我知道它没有.

I'm not asking if it does. I know that it doesn't.

我很好奇原因.我已经阅读过支持文档，例如关于在 MySQL 中使用空值 但他们真的没有给出任何理由.他们只是重复你必须使用is null"的口头禅.

I'm curious as to the reason. I've read support docs such as as this one on Working With Nulls in MySQL but they don't really give any reason. They only repeat the mantra that you have to use "is null" instead.

这一直困扰着我.在执行动态 SQL 时(那些很少需要执行的情况)，将null"传递到 where 子句中会容易得多，如下所示:

This has always bothered me. When doing dynamic SQL (those rare times when it has to be done) it would be so much easier to pass "null" into where clause like this:

@where = "where GroupId = null"

这将是常规变量的简单替代.相反，我们必须使用 if/else 块来执行以下操作:

Which would be a simple replacement for a regular variable. Instead we have to use if/else blocks to do stuff like:

if @groupId is null then
     @where = "where GroupId is null"
else
     @where = "where GroupId = @groupId"
end

在更大的更复杂的查询中，这是一个巨大的痛苦.SQL 和所有主要 RDBMS 供应商不允许这样做是否有特定原因?它会造成某种关键字冲突或价值冲突吗?

In larger more-complicated queries, this is a huge pain in the neck. Is there a specific reason that SQL and all the major RDBMS vendors don't allow this? Some kind of keyword conflict or value conflict that it would create?

很多答案的问题(在我看来)是每个人都在 null 和我不知道值是什么"之间建立了等价关系.这两件事有很大的不同.如果 null 意味着有一个值但它是未知的"，我会 100% 同意 null 不能相等.但是 SQL null 并不意味着.这意味着没有没有的价值.任何两个为空的 SQL 结果都没有值.无值不等于未知值.两种不同的东西.这是一个重要的区别.

The problem with a lot of the answers (in my opinion) is that everyone is setting up an equivalency between null and "I don't know what the value is". There's a huge difference between those two things. If null meant "there's a value but it's unknown" I would 100% agree that nulls couldn't be equal. But SQL null doesn't mean that. It means that there is no value. Any two SQL results that are null both have no value. No value does not equal unknown value. Two different things. That's an important distinction.

编辑 2:

我遇到的另一个问题是其他 HLL 允许 null=null 非常好并适当地解决它.例如，在 C# 中，null=null 返回 true.

The other problem I have is that other HLLs allow null=null perfectly fine and resolve it appropriately. In C# for instance, null=null returns true.

推荐答案

默认关闭的原因是null在业务中真的不等于null感觉.例如，如果您要加入订单和客户:

The reason why it's off by default is that null is really not equal to null in a business sense. For example, if you were joining orders and customers:

select * from orders o join customers c on c.name = o.customer_name

将未知客户的订单与姓名未知的客户匹配起来没有多大意义.

It wouldn't make a lot of sense to match orders with an unknown customer with customers with an unknown name.

大多数数据库允许您自定义此行为.例如，在 SQL Server 中:

Most databases allow you to customize this behaviour. For example, in SQL Server:

set ansi_nulls on
if null = null  
    print 'this will not print' 
set ansi_nulls off
if null = null  
    print 'this should print'

这篇关于为什么SQL不支持“= null"?而不是“为空"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！