60

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

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ?;
ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
Fseee
  • 2,476
  • 9
  • 40
  • 63

4 Answers4

89

Try this:

SELECT * 
FROM MY_TABLE 
WHERE @parameter IS NULL OR NAME = @parameter;
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • How to use this in PHP ? – prograshid Aug 22 '17 at 05:08
  • @prograshid - Just execute the query in PHP – Mahmoud Gamal Aug 22 '17 at 07:45
  • I tried to exucute query string but its giving when accessing the result. Saying undefined function call fetch_assoc – prograshid Aug 22 '17 at 10:10
  • 1
    @prograshid This has nothing to do with MySQL, you have a problem in your php code. Just ask a new question here about it. – Mahmoud Gamal Aug 22 '17 at 10:17
  • If you happen to have other predicates (something) I would add parentheses to ensure the evaluation order is what you intend [see https://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or ] for example: WHERE something AND (@parameter IS NULL OR NAME=@parameter) – andrew pate Feb 15 '21 at 15:49
45

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);
Robert
  • 25,425
  • 8
  • 67
  • 81
  • 5
    This won't work if there are nulls in `NAME`. But for non-nullable columns this is the way to go =) (EDIT: at least not in SQL Server) – Johan Hjalmarsson Aug 13 '15 at 07:31
  • Beware that this can produce slower queries than conditioning the SQL to not contain the where clause when the parameter is null. – Kram Jun 20 '17 at 13:40
2

The foll. query will handle the case where the Name (table column value) can also be NULL:

SELECT NAME, SURNAME FROM MY_TABLE WHERE COALESCE(NAME,'') = COALESCE(?,NAME,'');
variable
  • 8,262
  • 9
  • 95
  • 215
0
SELECT NAME
FROM MY_TABLE
WHERE NAME LIKE CASE WHEN ? IS NOT NULL THEN ? ELSE '%' END

This will work perfectly but it will return only the not null values.

Pushkar Saxena
  • 1
  • 1