The Old Joel on Software Forum: Part 3 (of 5)

and or or

We have a program here that allows users to create a 'SQL like' statements. I'm having trouble explaining to one user when to use and and when to use or.

Please don't start on should this user be doing this, etc.

I'm just looking for a way to explain when to use which.

Thanx in Advance

Stevie Ray
Wednesday, November 26, 2003

http://www.google.com/advanced_search?hl=en

"with ALL of the words"

"with AT LEAST ONE of the words"

That pretty much sums it up.

Brad Wilson (dotnetguy.techieswithcats.com)
Wednesday, November 26, 2003

The meaning of "and" and "or" in your database is essentially the same as it is in real life.

Seriously, if this person is so intellectually challenged that they don't understand the difference between "apple and banana" and "apple or banana" I certainly don't want them any where near my computers.

Mayor McCheese
Wednesday, November 26, 2003

Just out of interest, is English their first language?

If not, you might want to localize.

Tapiwa
Wednesday, November 26, 2003

"and": tends to reduce the number of rows returned.
"or": tends to increase the number of rows returned (like running separate queries for each).

njkayaker
Wednesday, November 26, 2003

Get them to write down the criteria in english, and have them decide on the following for when there are two conditions

is it Both Condition A AND Condition B

alternatively

Either Condition A OR Condition B

If there are more than two conditions have them use the following

All Condition A, Condition B, Condition C, etc are true (use And)

Any of Condition A, Condition B, Condition C, etc are true (use Or)

Simon Lucy
Wednesday, November 26, 2003

It can be confusing for some users

"apple AND orange"

They may interpret it as "I want both apple AND orange"

Wednesday, November 26, 2003

Most of the time when users want OR it's for the same field.

In other words

Total < 1.00 AND State = 'NY'
is common

Total < 1.00 AND (State = 'NY' OR State = 'NH')
is common

Total < 1.00 OR State = 'NY'
is rare.

If you want a clear UI that handles common cases elegantly without confusing people about AND and OR, you can design a UI which always assumes "AND" but which lets you MultiSelect the matching values.

The risk is that not every possible SQL query can be expressed. In most cases that's OK.

Joel Spolsky
Wednesday, November 26, 2003

For an example look at
http://www.corcoran.com/property/sales_search.asp

It's doing "AND" implicitly but you can multiselect in certain fields to get the common uses of "OR"

Joel Spolsky
Wednesday, November 26, 2003

As a general rule, when explaining to users who Just Don't Understand:

AND decreases the number of result you get.

OR increases the number of results you get.

Wednesday, November 26, 2003

I think it isn't a surprising thing that someone is confused by what we programmers take for granted. I can see how someone might interpret AND and OR to do the same thing.

I want to show records that satisfy Condition 1 AND Condition 2 *can* translate to...

I want to show records that satisfy Condition 1, AND I want to show records that satisfy Condition 2.

See, someon can go into it with a misleading notion of what AND is supposed to mean in the query.

Patrick Lioi
Wednesday, November 26, 2003

This is why, generally speaking, you should never give them the choice. Google is AND by default, and that's the appropriate decision.

Choices are bad at least as they are good. In many cases, a "choice" is either the result of an engineer who thinks "more is better", or a political argument that couldn't end in a firm decision. Neither of those is, generally speaking, good. :)

Buck up and make the choice for them.

Brad Wilson (dotnetguy.techieswithcats.com)
Wednesday, November 26, 2003

Just explain it in English.

If (I have milk AND cereal AND a spoon)
I get to eat.

Do you eat if you have milk, cereal, but no spoon?
Do you eat if you have only 2 items?

If (I have a casette OR a CD)
I put on some music.

Do you listen to music if you have a casette, but no CD?
Do you listen to music if you have a CD, but no casette?
Do you listen to music if you have either?

Bella
Wednesday, November 26, 2003

>>
The meaning of "and" and "or" in your database is essentially the same as it is in real life.

Seriously, if this person is so intellectually challenged that they don't understand the difference between "apple and banana" and "apple or banana" I certainly don't want them any where near my computers.
<<

Isn't it interesting how people who toss around terms like "intellectually challenged" typically have no clue? If "and" and "or" mean the same to you in real life as they do when applied to logical operations in programming, you are quite confused!

For example, if you ask someone in "real life" for an "apple and banana", you can expect to get two things back -- an apple and banana back. If you ask a database for an "apple and banana", you can expect to get nothing back because there is no such thing as an "apple and banana" (genetic engineering aside). Real life 'and' tends to be more like logical 'or', real life 'or' tends to be more like logical 'exclusive or'.

SomeBody
Wednesday, November 26, 2003

Tell them "OR" means "and" and "AND" means "both" :)

Stephen Jones
Thursday, November 27, 2003

Somebody is right, Steven too.
People who are used to boolean meanings have a hard time understanding why people don't get this. But it is they who are "special", in the sense that they have lost sight of the meaning of these words in natural language.

Practical Geezer
Thursday, November 27, 2003

I think Brad said all that needs to be said.

and: "ALL of the terms"
or: "ANY of the terms"

Mr Jack
Thursday, November 27, 2003

So when I am looking for a book on cooking, and a book on fishing, what do I tell Amazon to search for?

Practical Geezer
Thursday, November 27, 2003

A book on cooking fish? :)

NoName
Thursday, November 27, 2003

"So when I am looking for a book on cooking, and a book on fishing, what do I tell Amazon to search for?"

Leaving out the fact that Amazon's search now sucks, you would perform two searches. That's the way most people work. Search for your book on cooking, peruse through, pick one, throw it in your card, then repeat for the book on fishing.

Honestly, does it have to be so complex? Engineers love to complicate things. It's almost like, just because they can think of edge cases, that that suddenly makes those cases important. Sometimes, an edge case is best left alone, and you optimize for the vast majority of general usage.

Brad Wilson (dotnetguy.techieswithcats.com)
Friday, November 28, 2003

Although what you say is correct, Brad, the point was that in the example AND in the real world translates to OR in boolean logic.
As an example to explain why boolean logic is not at all logical to people who are not familiar with it.

Practical Geezer
Sunday, November 30, 2003

In real life, "or" can include the possibility of both, or not, depending on the context.

"Do you want the red car or the blue one?" (but no both)
"To get ahead in life you need to be rich or beautiful." (or both)

J
Monday, December 1, 2003