Is the `AND` used in `BETWEEN` the same as the `AND` operator between conditions?

Question

Is the AND used in BETWEEN the same as the AND operator used between multiple conditions?

Answer

No, although they may be assumed to be the same thing, the AND used with a BETWEEN, like

BETWEEN 1990 AND 1999

is not quite the same AND used when combining multiple conditions. When used in a BETWEEN statement, we are not combining two separate conditions, but providing a range of values to obtain the values within that range.

However, we can easily rewrite a BETWEEN to one with two conditions, like these queries which would be identical.

SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;

SELECT * 
FROM movies
WHERE year >= 1990
AND year <= 1999;
15 Likes
SELECT *
FROM movies
WHERE year IS NOT NULL
   AND year BETWEEN 1985 AND 1989
   AND genre = 'horror';

The first AND is an operator used between multiple conditions, as part of the WHERE clause.

The second AND is a part of the BETWEEN operator, also a part of the WHERE clause.

I understand correctly?

14 Likes

I think it should be " use : ‘1985’ , ‘1989’ ?

Hello,
no, as mentioned in the last hint of exercise 10:

Also, numeric values ( 1985 ) don’t need to be wrapped with single quotes, whereas string values do ( 'horror' ).

4 Likes

But I tried the below one and it doesn’t work for year.

SELECT *

FROM movies

WHERE year > '2000' AND genre = 'romance' OR genre = 'comedy';

This has to do with Operator Precedence, here is the docs:

MySQL :: MySQL 8.0 Reference Manual :: 12.4.1 Operator Precedence

the AND operator is evaluated first, seems you might want to introduce brackets

5 Likes

Hi,
How can we write WHERE statement with more than 2 AND conditions.
For example the below code is not working for me.

SELECT *
FROM movies
WHERE year > 2000
AND genre = ‘romance’
AND genre = ‘comedy’;

It is possible to have 2 AND conditions, but not in the manner you’re trying to use it.
In this table each movie belongs to only one genre, so it’s not possible to have a movie with a genre of ‘romance’ and comedy at the same time.
Replace the second “AND” WITH “OR” so you can get movies that belong to both genres.
SELECT *
FROM movies
WHERE year > 2000
AND genre = ‘romance’
OR genre = ‘comedy’;

3 Likes

hi ruronite,
SELECT *
FROM movies
WHERE year > 2000
AND genre = ‘romance’
OR genre = ‘comedy’;

is also not working as expected:


anybody knowing why?
thanks

OR displays results if any condition is true. (which is why you’re getting those comedy film results. The year condition isn’t applied). Think of it as the OR condition is standing alone. Your results will just be all comedy films from the table.

With AND both of those conditions have to be true (year > 2000 AND film = ‘romance’), which is why you get those results.

If you want both those genres and films made after 2000, then something like this would work:
Edit: I’m not sure if you’ve learned about using the IN operator yet.

Summary
SELECT * 
FROM movies 
WHERE year > 2000 AND
genre IN ('romance', 'comedy');
2 Likes

Hi lisalisaj! thanks for your tip. your code did exactly what expected. never heard about the IN operator, but now i do :slight_smile:

2 Likes

@core2941988678
This has to do with the order of execution, the “AND” is evaluated before “OR”. You need to separated the “OR” expression with a bracket so that it is evaluated first. I think i missed the bracket in my original comment Something like:

WHERE year > 2000
AND (genre = “romance” OR genre = “comedy”)

Or you use the “IN” operator like at @lisalisaj pointed out.

Can you send me the link to the exercise so i can look at it very well?

1 Like

@roisql Yes I think that’s a really good demonstration and quite correct. The third AND is again part of the WHERE clause used between conditions again.