SQLite null column alias question

[jon86]

Hi,

I was wondering if anyone could help me improve a SQLite query.

I have a table with a nullable column. Sometimes I want to run a SQL query with a value for the nullable column, and sometimes I run the query without a value. Given the size and complexity of the query that I am writing, I'd like to re-use the same SQL for both queries.

A primitive example follows.

CREATE TABLE product (
  '_id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  'name' TEXT NOT NULL,
  'alt_name' TEXT
)

INSERT INTO product ('name','alt_name') VALUES ('A','Fred')
INSERT INTO product ('name','alt_name') VALUES ('B',NULL)

Select all query

SELECT
   name,
   alt_name
FROM
   product

returns

name | alt_name
A | Fred
B | NULL

Select query with alt_name value

SELECT
   name,
   alt_name
FROM
   product
WHERE alt_name='Fred'

returns

name | alt_name
A | Fred

When I was writing the query to get rows without specify a value for the alt_name column, I thought that I could use the IFNULL function and give a value.

SELECT
   name,
   IFNULL(alt_name, 'null') as alt_name
FROM
   product
WHERE alt_name='null'

Unfortunately, this does not return any results. However, if I give the column an alias that is different from the name of the column, this does work.

SELECT
   name,
   IFNULL(alt_name, 'null') as alt_name_ifnull
FROM
   product
WHERE alt_name_ifnull='null'

name | alt_name
B | NULL

Is there a way to write the query so that I do not have to use a column alias but the query can still be reused for both not null and null values for the alt_name column?


Thanks,
Jon

 

[Trucamo]

You want to be using IS NULL instead of ='null' as that will be looking for the character sequence 'null' rather than a NULL field.

I would be writing the query as follows (using a parameter @altName):

SELECT
   name,
   alt_name
FROM
   product
WHERE (@altName IS NULL AND alt_name IS NULL) OR 
   (@altName IS NOT NULL AND alt_name=@altName)

When @altName is null it will only return results where alt_name is null, when @altName has a value, it will only return results where alt_name matches.

This is how I would do it in C#, other languages may differ in how they use parameter based queries.

 

[jon86]

Of course, thanks very much! I completely forgot about the OR keyword. How n00b of me. With that said, it was 1:36am local time for me when I was posting the thread and I was more than a bit tired.