Skip to content
Advertisement

sql query if parameter is null select all

Can the following query be modified to return all records if the ? is null?

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ?;

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement