mysql if null 全选,SQL查询如果参数为空全选

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

解决方案

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);

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:

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