sqllite - sql question 101

Question

Hi,

I would do something like this:

1. select * from cars_table where body not equal to null.
2. select * from cars_table where values not equal to null And id = "3"

I know the syntax for 'not equal' is <>, but I get an empty results.

For the second part, I want to get a result set where it only returns the columns that have a value. So, if the value is null, then don't include that column.

Thanks

Answer 1

You cannot use equality operators for nulls, you must use is null and is not null.

So your first query would be:

select * from cars_table where body is not null;

There's no easy way to do your second operation (excluding columns that are null). I'm assuming you mean don't display the column if all rows have NULL for that column, since it would be even harder to do this on a row-by-row basis. A select statement expects a list of columns to show, and will faithfully show them whether they are null or not.

The only possibility that springs to mind is a series (one per column) of selects with grouping to determine if the column only has nulls, then dynamically construct a new query with only columns that didn't meet that criteria.

But that's incredibly messy and not at all suited to SQL - perhaps if you could tell us the rationale behind the request, there may be another solution.

Answer 2

Comparing to NULL is not done with <>, but with is null and is not null :

select * 
from cars_table
where body is not null

And :

select *
from cars_table
where values is not null
    and id = '3'

NULL is not a normal value, and has to be dealt with differently than standard values.

Answer 3

In SQL, NULL is an unknown value. It is neither equal-to nor not-equal-to any other value. All comparison operators (=, <>, <, >, etc.) return false if either of the values being compared is NULL. (I don't know how old you are, so I can't say that you're 24, but I also can't say that you're not 24.)

As already mentioned, you have to use IS NULL or IS NOT NULL instead when testing for NULL values.

Answer 4

Thank you all for your answers.

Well, I have a table with a bunch of columns. And I am going to search a particular car, and get the values for that car...

car | manual | automatic | sedan | power brakes | jet engine

honda | true | false | false | true | false

mazda | false | true | false | true | true

So, I want to do -> select * from car_table where car='mazda' and values not equal to false

So then I just iterate over the cursor result and fill up the table with the appropriate columns and values. Where values are columns. I guess I replace values with * for columns

I know I could do it programmatically, but was thinking I could do this just by sql