Can the following query be modified to return all records if the ? is null?
SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ?;
Add a comment
|
4 Answers
Try this:
SELECT *
FROM MY_TABLE
WHERE @parameter IS NULL OR NAME = @parameter;
5 Comments
prograshid
How to use this in PHP ?
Mahmoud Gamal
@prograshid - Just execute the query in PHP
prograshid
I tried to exucute query string but its giving when accessing the result. Saying undefined function call fetch_assoc
Mahmoud Gamal
@prograshid This has nothing to do with MySQL, you have a problem in your php code. Just ask a new question here about it.
andrew pate
If you happen to have other predicates (something) I would add parentheses to ensure the evaluation order is what you intend [see stackoverflow.com/questions/1241142/… ] for example: WHERE something AND (@parameter IS NULL OR NAME=@parameter)
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);
2 Comments
Johan Hjalmarsson
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)
Kram
Beware that this can produce slower queries than conditioning the SQL to not contain the where clause when the parameter is null.
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,'');
Comments
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.
