CODEWITHSUNDEEP
mysqlsqldatabase
MathsandCodes
MathsandCodes

Reputation: 3

what is the difference between 2 similar queries

I was expecting identical outputs but when I ran them separately, got different outputs, can someone explain the differences please? Thank you!

The first is:

SELECT * FROM Products
WHERE Price > 20

The second is:

SELECT * FROM Products
WHERE Price > SOME (SELECT Price FROM Products WHERE Price > 20);

enter image description here

Upvotes: 0

Views: 162

Answers (3)

MathsandCodes
MathsandCodes

Reputation: 3

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_any2&ss=-1

I think I have reached the answer. The first query will return all records where the prices' values are greater than 20. The second query will return all records where the prices' values are 2 steps greater than 20. So, it will ignore the following prices 20 and 21 twice.

Upvotes: 0

Rajat kashyap
Rajat kashyap

Reputation: 622

The confusion part is the some operator in your query.

Suppose using greater than ( >) with SOME means 
greater than at least one value.

That's how the some operator works. For better understanding https://www.w3resource.com/sql/special-operators/sql_some.php

Upvotes: 1

Willem Renzema
Willem Renzema

Reputation: 5187

The second query will exclude the minimum result of the first query.

If, for example, 23 is the lowest price greater than 20, then the second query will only look for results with a price greater than 23, and so not include that row (and any others with a price of 23.)

Upvotes: 2

Related Questions