PostgreSQL 10 SELECT WHERE statements

user_13279304 0
Viewed 43

I am currently new to PostgreSQL and learning on version 10.14. Was working on SELECT commands using the WHERE operator and confused while using the NOT option.

Objective: Retrieve data between a particular range; "marks less than equal to 35 & more than equal 60".

Issue: the difference between the two syntaxes, as results are different?

SELECT name FROM science_class WHERE science_marks not between 35 and 60;   
SELECT name FROM science_class WHERE science_marks<=35 AND science_marks>=60;

Thank you, Hermes Schema

range postgresql
3 Answers

These queries below should give the same results: the data outside of the range. Note that BETWEEN operator includes the endpoints. Also, you need OR, not AND in the second statement:

SELECT name FROM science_class WHERE science_marks NOT BETWEEN 35 AND 60;   
SELECT name FROM science_class WHERE science_marks < 35 OR science_marks > 60;

To include the data in the range, rather than outside of the range, use any of these:

SELECT name FROM science_class WHERE science_marks BETWEEN 35 AND 60;   
SELECT name FROM science_class WHERE science_marks >= 35 AND science_marks <= 60;
user_9676210

You almost have the right idea.

TL;DR : your second query should be SELECT name FROM science_class WHERE science_marks < 35 OR science_marks > 60;

  1. It should be OR instead of AND because it should meet either one of those condition. With And, both condition must be met which is impossible

  2. When you say NOT BETWEEN 35 AND 60, you are excluding the 35 and 60 so you should use < or > and not <= or >=

user_105410610

Lower & upper ranges are included in between and you've included them in your exclusion (if you see what I mean). Should be <35 or >60 to be equivalent.

user_11667740
Related