sql查询如果参数为空，请选择全部

Question

我有以下问题：

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ?;

现在我想如果是“？”为空，则必须选择所有记录。我如何在SQL中实现这一点？谢谢

Answer 1

您还可以使用函数IFNULL、COALESCE、NVL、ISNULL来检查空值。这取决于您的RDBMS。

MySQL

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = IFNULL(?,NAME);

或

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = COALESCE(?,NAME);

ORACLE

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = NVL(?,NAME);

SQL Server / SYBASE

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ISNULL(?,NAME);

Answer 2

试试这个：

SELECT * 
FROM MY_TABLE 
WHERE @parameter IS NULL OR NAME = @parameter;

Answer 3

福尔。查询将处理Name (表列值)也可以为NULL的情况：

SELECT NAME, SURNAME FROM MY_TABLE WHERE COALESCE(NAME,'') = COALESCE(?,NAME,'');