Can we compare values of two columns in a WHERE clause?

Question

Can we compare values of two columns in a WHERE clause?

Answer

Yes, within a WHERE clause you can compare the values of two columns.

When comparing two columns in a WHERE clause, for each row in the database, it will check the value of each column and compare them.

Example

/* 
This will return all rows where the value in the 
x column is greater than the y column value. 
*/

SELECT x, y
FROM coordinates
WHERE x > y;
20 Likes

Can you have two constraints? F
Example

SELECT *
FROM movies
WHERE 4 < imdb_rating < 5;

or

SELECT *
FROM movies
WHERE imdb_rating < 5
WHERE year > 2014;

Thanks

16 Likes

we can use and if both conditions needs to be met, otherwise there is or if one of the conditions has to be met:

where condition1 and condition2
where condition1 or condition2
35 Likes

Thank you for your reply! When running multiple regression analysis, it is common to use more than one constraint…as each regressor has some condition!

You can also use NOT keyword
for examples we may want all student that aren’t 18

SELECT *
FROM student
WHERE NOT age >  18;

or simply you do

SELECT *
FROM student
WHERE age < 18;
11 Likes

yes you can have 2 conditions enclosed in a where clause but it should be written as :
SELECT *
FROM movies
WHERE imdb_rating >4 and Imdb_rating <5 ;
this will provide you imdb_rating values between 4 and 5. if you try your approach described above, the results will be number above 4 only while ignoring the second condition.

The second one will not give you any result due to syntax error.
it will be best to query that as :
SELECT *
FROM movies
WHERE year >2014 and imdb_rating < 5;

2 Likes

Don’t forget that sometimes you also want the value you’re trying to filter for as well. I’ve made this mistake more than once in forgetting to also include the initial value in my search:

SELECT * FROM movies WHERE year >= 2014 AND imdb_rating >= 4

2 Likes

Thanks for the helpful answers! May I know why

WHERE 4 < imdb_rating < 5

is not working?

Because it is a logical expression, and you can use logical expression like it.

1 Like