SQL查询如果参数为空全选 [英] sql query if parameter is null select all

问题描述

我有以下查询:

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ?;

现在我想要的是'?'如果为NULL，则必须选择所有记录. 如何在SQL中实现呢? 谢谢

Now I want that if '?' is NULL, all records have to be selected. How can I achieve this in SQL? Thanks

推荐答案

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

You can also use functions IFNULL,COALESCE,NVL,ISNULL to check null value. It depends on your 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);

这篇关于SQL查询如果参数为空全选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！