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 SQL Basics Finding the Data You Want Review & Practice with SQL Playgrounds

Posted by Elaine Tran
Elaine Tran
809 Points

Is there a better way to solve "Find all the invalid ratings in one query." I did NOT IN (1,2,3,4,5).

-- Review ratings should be only be 1-5. Find all the invalid ratings in one query

SELECT * FROM reviews WHERE rating NOT IN (1, 2, 3,4,5);

It worked, but I'm wondering if there's a better way. Thanks in advance.

4 Answers

Joel Bardsley
Joel Bardsley
31,249 Points
Joel Bardsley
Joel Bardsley
31,249 Points

You could use the BETWEEN operator, which would make the query:

SELECT * FROM reviews WHERE rating NOT BETWEEN 1 AND 5;
Elaine Tran
Elaine Tran
809 Points

Thank you Joel! :)

Diana Ci
Diana Ci
18,672 Points
Diana Ci
Diana Ci
18,672 Points

SELECT * FROM reviews WHERE rating < 1 OR rating > 5;

sradms0
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
sradms0
Treehouse Project Reviewer
sradms0
.a{fill-rule:evenodd;}techdegree seal-36
sradms0
Treehouse Project Reviewer 
SELECT * FROM reviews WHERE rating NOT BETWEEN 1 AND 5 OR rating IS NULL;
Mohammed Safiulla D
Mohammed Safiulla D
17,826 Points
Mohammed Safiulla D
Mohammed Safiulla D
17,826 Points

I guess it's always better to be explicit when coding however I tried for columns where information was NULL (for example the year_released in movies table; there are no reviews with null ratings). I found that NOT BETWEEN removes NULL values by default. The following 2 lines give the same result :

select * from movies where year_released  not between 2000 and 2005;
select * from movies where year_released  not between 2000 and 2005 or year_released is null;

Still, I would do what you did and always be explicit while coding :)

Codeon Tech
Codeon Tech
1,150 Points
Codeon Tech
Codeon Tech
1,150 Points

SELECT * FROM reviews WHERE rating IN (5);