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

 

[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.