Query By Example

A user query interface technique whereby you have a template form with empty fields, and only those fields entered by the user become part of the query, usually joined by AND. Fancier variations use symbol codes or pull-down boxes for partial (LIKE) match operators and/or ranges. Example operators include "equals", "starts-with", "ends-with", "contains", etc.

Simple Ascii Example: 

  .....Name: Bob
  ..Address:
  .....City:
  ....State: TX
  ..Zipcode:
(Dots to prevent TabMunging)

Resulting SQL: 

  SELECT * FROM Contacts WHERE Name='Bob' AND State='TX'
Another variation is to control which columns are displayed in which order. Blank orders indicates that it is not displayed. Example: 

  column     |order|   value
  -----------|-----|-------------------
  .....Name: |  1  | Bob
  ..Address: |  3  |
  .....City: |     |
  ....State: |  2  | TX
  ..Zipcode: |     |
Query By Example originated in IBM with the QBE system. Unlike the examples above, the query forms were arranged with the column names running along the top. The user could enter several example rows and the result set consisted of all rows in the table that matched one or more of the example rows. For example, 

  | CONTACTS | Name  | Address   | City       | State | Zipcode |
  -----------|-------|-----------|------------|-------|---------|
  |          | Bob   |           |            | TX    |         |
  |          | Carol |           | Fort Worth |       |         |
  |          | Ted   |           |            |       | 75039   |
  |          | Alice | Southfork |            |       |         |
So across is "and" and up-down is "or", I assume. Thus the above would generate something like: 

  ... name in ('Bob','Carol','Ted','Alice') and Address='Southfork' and City='Fort...
Desktop databases like Access and Paradox have QBE-like query interfaces although Paradox was a lot more like QBE than Access is, including its use of link elements to join the rows.

We could also perhaps put the display selection and ordering in as such: 

  | CONTACTS | Name  | Address   | City       | State | Zipcode |
  -----------|-------|-----------|------------|-------|---------|
  |    2     |  1    |     3     |            |       |   4     |
  -----------|-------|-----------|------------|-------|---------|
  |          | Bob   |           |            | TX    |         |
  |          | Carol |           | Fort Worth |       |         |
  |          | Ted   |           |            |       | 75039   |
  |          | Alice | Southfork |            |       |         |
I find though that in order to facilitate "or's" like this, we make it harder or more confusing to allow "starts with", "contains" etc., usually via pull-down lists. Wild-card symbols can be added instead, but such can confuse the user quite a bit. We can instead do this: 

  Contacts [...........|V] [.........................]
  Names    [is-one-of..|V] [Bob, Carol, Ted, Alice...]
  Address  [equals.....|V] [Southfork................]
  Foo      [contains...|V] [blah.....................]  // not in above example
  Bar      [starts-with|V] [fibble...................]  // not in above example
Here "[...|V]" is a drop-down list and "[....]" is an entry box. (AsciiArtGuiShorthand) [Dots above to prevent TabMunging]

"Range" can be similar to "is-one-of" in that a start and end-range is separated by a comma. However, such is not usable on content with commas in it. (More on "compare types" is given below.)

Super-fancy QBE starts to morph into UserQueryInterfaces, which are generally a form or extension of QBE for power-users.

There is no such thing as "super-fancy QBE". QBE is not a general category of interfaces, but a specific visual query language invented by Moshe Zloof. See http://portal.acm.org/citation.cfm?id=1499914 Obviously, other visual query languages have been inspired by it and developed independently of it.

I believe it has "drifted" from the original definition into a more general meaning, sort like "Xerox", "Band-Aide", and "Kleenex" representing general product categories instead of specific brands.

One potential problem with QBE is "run-away queries". Being open-ended, it is possible that users can create queries that take a long time or consume a lot of resources. One approach to reducing the chances of this is limiting searches to indexed columns and avoiding partial-column searches such as LIKE. Some RDBMS can use indexes for certain kinds of partial searches. However, this reduces the power of QBE. Sometimes you may assign designated power-users more columns or column filter features and have more specific access to other departments or a wider audience. If other departments need more powerful searches, they then contact the designated power user(s).

Result sets should also be limited. Generally the result set should be limited to between 200 and 1,000 records. The SQL syntax to put an upper limit on returned rows varies widely between RDBMS vendors. When the quota is reached, a message at the bottom of the displayed results can say something like: 

 "Warning: This query has exceeded its display quota. 
  Please narrow your search to avoid display quotas."
Actually, I usually put "may have exceeded" because one cannot always efficiently tell whether the row count is an exact coincidental quota match or an excess. A deluxe system may have a hyper-link on "display quota" to explain what is going on and offer the user tips.

The usual trick around the "may have exceeded" problem is to fetch n+1 rows where n are needed, and to display a warning instead of the last one if the query indeed returns n+1 results. Possibly not elegant enough in one's book, but it does the job.

--top

I've found that the "range-and-list" operation can reduce the need for lists, ranges, OR's, and possibly greater-than, less-than, etc. I'll call it the "list-and-range" for now. It basically resembles the page range in MS-Office where you can mix ranges and a list. Example: "1,3,5-8,12-18,26". The dash indicates ranges, and commas indicate lists. It requires a fair amount of parsing, but reduces the total operations you need to define. It would automatically add quotes for string data. If the user wants to embed dashes and comma's, then require quotes around the items. However, most range-able codes don't need these, so quotes may be an overkill feature (unless you're making a generic widget for mass-markets). With this one can design a minimal set of matching operations:

Other suggestions:

The nature of the QBE ignore-if-blank convention makes these useful. There may also be "Null" or "Not-Null" options, but these don't mean much to non-techies. Thus, "empty" or "blank" is usually more descriptive.

See Also: WhereAndAnd, MinimalTable, UserQueryInterfaces

CategoryUserInterface, CategoryBusinessDomain

EditText of this page (last edited November 26, 2014) or FindPage with title or text search