0

I'm trying to use the NOT operator with ALL but does not compare as it should

I followed the following:

tablex contains for example:

+------+------+
| id   | name |
+------+------+
|    6 | a    |
|    7 | b    |
|    8 | c    |
|    9 | d    |
|   10 | e    |
+------+------+
5 rows in set (0.04 sec)

and tabley contains:

+------+------+
| id   | name |
+------+------+
|    4 | a    |
|    5 | b    |
|    7 | c    |
|    8 | d    |
+------+------+
4 rows in set (0.03 sec)

i've used:

SELECT id, name FROM tablex WHERE NOT id < ALL (SELECT id FROM tabley);

returns:

+------+------+
| id   | name |
+------+------+
|    6 | a    |
|    7 | b    |
|    8 | c    |
|    9 | d    |
|   10 | e    |
+------+------+
5 rows in set (0.00 sec)

the problem is that returns lower values than those of 'tabley' in some cases, It is very logical the solution using the operator >, but what is this about?

MindLerp
  • 378
  • 1
  • 3
  • 15

1 Answers1

2

If you're using the < operator, it seems like you want to make sure that id is smaller than the smallest id in the subquery. So to express that in SQL:

SELECT id, name FROM tablex WHERE NOT id < (SELECT min(id) FROM tabley)

Alternately, flipping around the negation:

SELECT id, name FROM tablex WHERE id >= (SELECT min(id) FROM tabley)
Matt Ball
  • 354,903
  • 100
  • 647
  • 710
  • ok, but one example: SELECT id, name FROM tablex WHERE NOT id < ANY (SELECT id FROM tabley); returns: +------+------+ | id | name | +------+------+ | 8 | c | | 9 | d | | 10 | e | +------+------+ 3 rows in set (0.11 sec) why works with 'ANY' but no with 'ALL'? – MindLerp Apr 02 '16 at 22:09
  • @nikomaster perhaps this might help illustrate: http://stackoverflow.com/a/2298576/139010 – Matt Ball Apr 02 '16 at 22:11
  • And this: _"The word `ALL`, which must follow a comparison operator, means “return `TRUE` if the comparison is `TRUE` for `ALL` of the values in the column that the subquery returns.”"_ http://dev.mysql.com/doc/refman/5.7/en/all-subqueries.html – Matt Ball Apr 02 '16 at 22:12