sql query if parameter is null select all

Question

Can the following query be modified to return all records if the `?` is null? ``` SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ?; ```

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

Solution