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