What's the difference between these two SQL queries?

user_11907629 0
Viewed 41

SELECT title, release_year, language FROM films WHERE release_year BETWEEN 1990 AND 2000 AND budget > 100000000 AND language = 'Spanish' OR language = 'French'

SELECT title, release_year, language FROM films WHERE release_year BETWEEN 1990 AND 2000 AND budget > 100000000 AND language = ('Spanish'OR language = 'French')

Why does the first query is receiving data for the years such as 1964, 1965? Because my BETWEEN command specifically asks for data in the range 1990 and 2000.

Can someone please help?

sql
2 Answers

Mind your parentheses. Query should be

SELECT title, release_year, language FROM films WHERE release_year BETWEEN 1990 AND 2000
AND budget > 100000000 AND (language = 'Spanish' OR language = 'French')
user_176450990

You need to consider the logical processing of and and or.

Consider where a=1 and b=2 or b=3

Does this mean a must be 1 and b must be 2 or 3, OR does it mean a must be 1 and b must be 2, OR b can be 3 with any value for a

Use parenthesis to be explicit:

where a = 1 and (b = 2 or b = 3)

If you properly separate the conditions on each line it makes it much easier to see the intention at a glance.

SELECT title, release_year, language 
FROM films 
WHERE release_year BETWEEN 1990 AND 2000
  AND budget > 100000000 
  AND (language = 'Spanish' OR language = 'French');

You can eliminate the use of OR here by simply using in

SELECT title, release_year, language 
FROM films 
WHERE release_year BETWEEN 1990 AND 2000
  AND budget > 100000000 
  AND language IN ('Spanish', 'French');
user_153326500
Related