What's the difference between these two SQL queries?-CodePudding

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?

CodePudding user response：

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')

CodePudding user response：

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');