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

Aamnah Akram
Aamnah Akram
17,549 Points
Posted by Aamnah Akram
Aamnah Akram
17,549 Points

Finding rows with any missing data in a table

-- Find all movies with any missing data

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

Is there a better way to find rows with any missing value than what i have done above? What if a table has lots of columns? Do i use plenty of OR conditions like above? or is there a better keyword/way of getting all rows with missing values in a table without having to type all the column names.

Many thanks in advance

3 Answers

Luke Vaughan
Luke Vaughan
15,258 Points
Luke Vaughan
Luke Vaughan
15,258 Points

This worked for me. 

SELECT * from movies WHERE id IN (title, year_released, genre) IS NULL;
Urooba Urooba
Urooba Urooba
3,565 Points
Urooba Urooba
Urooba Urooba
3,565 Points

can you please explain how this works?

Afrid Mondal
Afrid Mondal
6,255 Points
Afrid Mondal
Afrid Mondal
6,255 Points

I modified my code try this....

SELECT * FROM movies WHERE COALESCE(id,title,year_released,genre)
Aamnah Akram
Aamnah Akram
17,549 Points
Aamnah Akram
Aamnah Akram
17,549 Points

Tried that, doesn't work as expected. The result is 10 rows where 8 have no NULL values in them..

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

I'm a bit surprised that didn't cause an error. Coalesce just searches down the list of arguments and returns the first value that is not null (or null if they ALL are). But normally you'd still have to perform some test on it, like this:

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

...which would return only records that had ALL missing data, mostly likely to be none of them.

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 pretty much how you show it. You can eliminate the test for id, since you can assume any existing record would have one.

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.

Now, for commonly used queries, it might make sense to create a view. It 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;