= any Means Equal to Some Value

The = any operator is an existence check; it is equivalent to in.

For example, to find authors that live in the same city as any publisher, you can use either = any or in:

select au_lname, au_fname 
from authors 
where city = any 
   (select city 
    from publishers) 
select au_lname, au_fname 
from authors 
where city in 
   (select city 
    from publishers)

au_lname       au_fname 
-------------- -------------- 
Carson         Cheryl 
Bennet         Abraham

However, the != any operator is different from not in. The != any operator means “not = a or not = b or not = c”; not in means “not = a and not = b and not = c”.

For example, to find the authors who live in a city where no publisher is located:

select au_lname, au_fname 
from authors 
where city != any 
   (select city 
    from publishers)

The results include all 23 authors. This is because every author lives in some city where no publisher is located, and each author lives in only one city.

The inner query finds all the cities in which publishers are located, and then, for each city, the outer query finds the authors who do not live there.

Here is what happens when you substitute not in in the same query:

select au_lname, au_fname 
from authors 
where city not in 
   (select city 
    from publishers)

au_lname                au_fname 
--------------          ------------ 
White                   Johnson  
Green                   Marjorie
O’Leary                 Michael
Straight                Dick
Smith                   Meander
Dull                    Ann 
Gringlesby              Burt 
Locksley                Chastity 
Greene                  Morningstar  
Blotchet-Halls          Reginald
Yokomoto                Akiko
del Castillo            Innes 
DeFrance                Michel
Stringer                Dirk 
MacFeather              Stearns
Karsen                  Livia 
Panteley                Sylvia
Hunter                  Sheryl
McBadden                Heather
Ringer                  Anne
Ringer                  Albert

These are the results you want. They include all the authors except Cheryl Carson and Abraham Bennet, who live in Berkeley, where Algodata Infosystems is located.

You get the same results if you use !=all, which is equivalent to not in:

select au_lname, au_fname 
from authors 
where city != all 
   (select city 
    from publishers)