Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Maximiliane Quel
PLUS
Maximiliane Quel
Courses Plus Student 55,489 Points
Posted by Maximiliane Quel
Maximiliane Quel
Courses Plus Student 55,489 Points

Possible to shorten query for missing data in any column?

In the database practice stage 3 playground one of the objectives is to Find all movies with any missing data. The movies table has four columns: id, which is the primary key, title, year_released and genre. Let's say id can never be empty, then we could probably write:

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

Is there a shorter way of writing that though? I imagine if we had a large table with lots of columns it would be annoying to have to write each one out.

3 Answers

Steven Parker
Steven Parker
231,261 Points
Steven Parker
Steven Parker
231,261 Points

Verbosity is a common complaint about SQL. The clearest way to code this test, even with many columns, is just how you show it.

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.

Nathan's idea about views still retains the verbosity, but moves it out of view in the main query. In that case, you might end up with a short main query like this:

SELECT * FROM movies_with_missing_data;

but behind the scenes you have already done this:

CREATE VIEW movies_with_missing_data AS
SELECT * FROM movies WHERE title IS NULL 
                        OR year_released IS NULL 
                        OR genre IS NULL;
Nathan Tallack
Nathan Tallack
22,160 Points
Nathan Tallack
Nathan Tallack
22,160 Points

You have stumbled upon the reason views exist. Not covered in this course, but hopefully in a future one.

Views are very simple to setup, and are VERY powerful. Why don't you take a look at the docs and try setting up a view for the query you had.

Views are REALLY helpful once you get really really big joins. ;)

Maximiliane Quel
PLUS
Maximiliane Quel
Courses Plus Student 55,489 Points
Maximiliane Quel
Maximiliane Quel
Courses Plus Student 55,489 Points

Thank you both! That helps a lot. Difficult to decide who has the best answer :0/