SQL sql查询如果参数为空全选
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13474207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql query if parameter is null select all
提问by Franky
I've the following query:
我有以下查询：
SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ?;
Now I want that if '?' is NULL, all records have to be selected. How can I achieve this in SQL? Thanks
现在我想要，如果'？为 NULL，则必须选择所有记录。如何在 SQL 中实现这一点？谢谢
采纳答案by Robert
You can also use functions IFNULL,COALESCE,NVL,ISNULLto check null value. It depends on your RDBMS.
您还可以使用函数IFNULL, COALESCE, NVL,ISNULL来检查空值。这取决于您的 RDBMS。
MySQL:
MySQL：
SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = IFNULL(?,NAME);
or
或者
SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = COALESCE(?,NAME);
ORACLE:
甲骨文：
SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = NVL(?,NAME);
SQL Server/ SYBASE:
SQL Server/ SYBASE：
SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ISNULL(?,NAME);
回答by Mahmoud Gamal
Try this:
尝试这个：
SELECT *
FROM MY_TABLE
WHERE @parameter IS NULL OR NAME = @parameter;
回答by variable
The foll. query will handle the case where the Name (table column value) can also be NULL:
胡说八道。查询将处理 Name（表列值）也可以为 NULL 的情况：
SELECT NAME, SURNAME FROM MY_TABLE WHERE COALESCE(NAME,'') = COALESCE(?,NAME,'');
回答by Pushkar Saxena
SELECT NAME
FROM MY_TABLE
WHERE NAME LIKE CASE WHEN ? IS NOT NULL THEN ? ELSE '%' END
This will work perfectly but it will return only the not null values.
这将完美运行，但它只会返回非空值。
