Using != with ANY

I have the following query (MySQL):

SELECT col1, col2 FROM database1.table 
  ->WHERE col3 != ANY(SELECT col1 FROM database2.table) 
  ->ORDER BY this, that;

And I had hoped this would allow me to select col1 and col2 from a table in database1 where col3 (still in database1) does not equal anything from col1 in a table in database2.

Naturally, this wont work because SELECT col1 FROM database2.table returns more than one row, so if is equal to row1, then it's not equal to row2 so it's still returned.

Any thoughts on how to do this the right way?

---

Use NOT IN

SELECT col1, col2 FROM database1.table ->WHERE col3 NOT IN(SELECT col1 FROM database2.table) ->ORDER BY this, that;

but keep in mind that subselects are not optimized in MySQL, and if there are a lot of records in database1.table this would be slow. Faster way is to use JOIN - there are a lot of examples at SO

---

WHERE col3 NOT IN (SELECT col1 FROM database2.table)

---

你可以使用NOT IN运算符来表示这个SELECT col1，col2 FROM database1.table - > WHERE col3 NOT IN（SELECT col1 FROM database2.table） - > ORDER BY this，that;

链接地址: http://www.djcxy.com/p/75326.html

上一篇: MySQL布尔匹配/反对中的REGEXP用法

下一篇: 使用！=和ANY