Is it better to use equal or not equal when making a query?

Question

I'm working with a MySQL database but this question could apply to SQL, Oracle, etc.

I have a table with products and one of the columns is the brand, I give the user the possibility of selecting which brands he wants. And here comes my question, is it better to make a query selecting all results from that table that match the selected brands or to make a query selecting all results from that table that don't match the unselected brands?

Thus,

is it more efficient/faster (or the same) to look for results equal to something or not equal? and why...

Answer 1

Performance is one reason to use =. There are two components to this. First is indexing, where = comparisons provide for more powerful indexing capabilities. The second is partitioning. Although unlikely on a column that takes just a handful of values, = is better for resolving partitions.

Another reason is semantic. The presence of NULLs can be confusing. Consider the two comparisons:

where col = 'x'
where col <> 'x'

Both of these where clauses filter out values where col is NULL. This makes total sense with the =. However, even after you know the rules, it is a bit confusing with the <>. Intuitively, we think "NULL is not equal to "x", so it should be true". In fact, NULL means an unknown value, and an unknown value could be equal to 'x', so the statement could be true; in fact, it returns NULL which is filtered out.