Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases

Keeper of the Cog Rebecca
Keeper of the Cog Rebecca
480 Points

The IN command question in SQL

If the IN command in SQL can be used to compare the same variable against multiple values to simplify code, can the opposite be done?

I'm more interested in simplying code by comparing multiple variables against the same value....

For example,

SELECT * FROM movies WHERE year_released, genre IS NULL;


I want to be able to look through a table and Select records where any of the information in any of the columns is blank. As an example... because sometimes I only want to filter through 2 columns and see if any information in those two columns IS NULL and simplifying the code

********End of Line

2 Answers

Steven Parker
Steven Parker
221,585 Points

There's a function called COALESCE that will return the first non-null in a list (of any size), or null if all of them are null. So you could write your query this way:

SELECT * FROM movies WHERE COALESCE(year_released, genre) IS NULL;

This would test if ALL of them are null. Now to test if ANY of them are null is trickier. Anything I can think of that would make the testing more compact would not be portable or clear.

Also remember that when using IN with a list, the list cannot include null.

Keeper of the Cog Rebecca
Keeper of the Cog Rebecca
480 Points

Yea I'm trying to avoid coding all of this

SELECT * FROM movies WHERE id IS NULL OR title IS NULL OR year_released IS NULL OR genre IS NULL;

Steven Parker
Steven Parker
221,585 Points

I'm not aware that COALESCE is discussed in the existing courses. But I know more are coming.

Keeper of the Cog Rebecca
Keeper of the Cog Rebecca
480 Points

Yup. I just requested they add more advanced courses for SQL. I just feel like if you code, SQL is just kind of expected of you (I guess it's somewhat intuitive if you know languages already), but I still need help with more advance queries and situations so I need training for my job. Can't get away with the basics that I know.

Steven Parker
Steven Parker
221,585 Points

Now depending on which SQL engine you use, there may be more compact ways to do the job, but they may have other drawbacks. For example. in Oracle you can say:

SELECT * FROM movies WHERE NVL2(title,1,0)+NVL2(year_released,1,0)+NVL2(genre,1,0) < 3;

...but that's not a whole lot shorter, it's much harder to understand, and certainly not portable to other SQL engines.

If this is something that you expect to use frequently, it might make sense to create a view, for example:

CREATE VIEW movies_with_missing_data AS
SELECT * FROM movies WHERE id IS NULL 
                        OR title IS NULL 
                        OR year_released IS NULL 
                        OR genre IS NULL;

And then, when you want to perform the actual query, you would just say this:

SELECT * FROM movies_with_missing_data;

And by the way, id is likely to be a primary key, and if so it cannot be null.

Keeper of the Cog Rebecca
Keeper of the Cog Rebecca
480 Points

Thank you that was very helpful. I hadn't learned about create view yet, so that's pretty nifty.

Yes very true, no reason to even select id in there since it's primary key.