PostgreSQL 10 SELECT WHERE statements

Question

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

ousecTic · Accepted Answer · 2020-09-08 05:10:45Z

2

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;

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
When you say NOT BETWEEN 35 AND 60, you are excluding the 35 and 60 so you should use < or > and not <= or >=

answered Sep 8, 2020 at 5:10

ousecTic

1,95514 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hermes-schema Over a year ago

that is correct! I am wanting to exclude by using the NOT BETWEEN operator. I am using the OR and not AND function which is my confusion.

Timur Shtatland · Accepted Answer · 2020-09-08 05:17:53Z

2

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;

edited Sep 8, 2020 at 5:17

answered Sep 8, 2020 at 5:10

Timur Shtatland

12.8k3 gold badges41 silver badges68 bronze badges

Comments

LoztInSpace · Accepted Answer · 2020-09-08 08:23:20Z

-1

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.

edited Sep 8, 2020 at 8:23

answered Sep 8, 2020 at 5:09

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

Collectives™ on Stack Overflow

PostgreSQL 10 SELECT WHERE statements

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related