Query by Example

Query by Example was devised by Moshé M. Zloof at IBM Research during the mid-1970s, in parallel to the development of SQL,{{cite journal |last=Zloof |first=M.M. |doi= 10.1147/sj.164.0324 |title=Query-by-Example: A data base language |journal=IBM Systems Journal |volume=16 |issue=4 |date=1977 |pages=324–343 |citeseerx=10.1.1.86.3485}} and influenced by the work on relational databases of Edgar Codd.{{Cite interview |interviewer=Burton Grad |title=RDBMS Plenary 1: Early Years |url=https://archive.computerhistory.org/resources/access/text/2013/05/102702562-05-01-acc.pdf |pages=23-24 |access-date=2025-05-30 |publisher=Computer History Museum |date=2007-06-12}} It is the first graphical query language, using visual tables where the user would enter commands, example elements and conditions. Many graphical front-ends for databases use the ideas from QBE today. Originally limited only for the purpose of retrieving data, QBE was later extended to allow other operations, such as inserts, deletes and updates, as well as creation of temporary tables.

The motivation behind QBE is that a parser can convert the user's actions into statements expressed in a database manipulation language, such as SQL. Behind the scenes, it is this statement that is actually executed. A suitably comprehensive front-end can minimize the burden on the user to remember the finer details of SQL, and it is easier and more productive for end-users (and even programmers) to select tables and columns by selecting them rather than typing in their names.

In the context of information retrieval, QBE has a somewhat different meaning. The user can submit a document, or several documents, and ask for "similar" documents to be retrieved from a document database [see search by multiple examples{{Cite book|last1=Zhu|first1=Mingzhu|last2=Wu|first2=Yi-Fang Brook|title=Proceedings of the 7th ACM international conference on Web search and data mining |chapter=Search by multiple examples |date=2014-02-24|publisher=ACM|pages=667–672|doi=10.1145/2556195.2556206|isbn=9781450323512|s2cid=8397529 }}]. Similarity search is based comparing document vectors (see Vector Space Model).

QBE represents seminal work in end-user development, frequently cited in research papers as an early example of this topic.

Currently, QBE is supported in several relational database front ends, notably Microsoft Access, which implements "Visual Query by Example", as well as Microsoft SQL Server Enterprise Manager. It is also implemented in several object-oriented databases (e.g. in db4o{{Citation | url = http://www.ibm.com/developerworks/java/library/j-db4o2.html | contribution = QBE | title = db4o | publisher = IBM | series = Developer works Java library| date = 27 March 2007 }}).

QBE is based on the logical formalism called tableau query, although QBE adds some extensions to that, much like SQL is based on the relational algebra.

An example using the Suppliers and Parts database is given here to illustrate how QBE works.

The term also refers to a general technique influenced by Zloof's work whereby only items with search values are used to "filter" the results. It provides a way for a software user to perform queries without having to know a query language (such as SQL). The software can automatically generate the queries for the user (usually behind the scenes). Here are two examples based on a Contacts table with the following text (character) columns: Name, Address, City, State, and Zipcode:

Contacts Query Form - Example A:

.....Name: Bob

..Address:

.....City:

....State: TX

..Zipcode:

Resulting SQL:

SELECT * FROM Contacts WHERE Name='Bob' AND State='TX';

Note how blank items do not generate SQL terms. Since "Address" is blank, there is no clause generated for it.

Contacts Query Form - Example B:

.....Name:

..Address:

.....City: Sampleton

....State:

..Zipcode: 12345

Resulting SQL:

SELECT * FROM Contacts WHERE City='Sampleton' AND Zipcode='12345';

More advanced versions of QBE have other comparison operator options, often via a pull-down menu, such as "Contains", "Not Contains", "Starts With", "Greater-Than", and so forth.

Another approach to text comparisons is to allow one or more wildcard character characters. For example, if an asterisk is designated as a wildcard character in a particular system, then searching for last names using "Rob*" would return (match) last names such as "Rob", "Robert", "Robertson", "Roberto", etc.

Contacts Query Form - Example C:

.....Name: Rob*

..Address:

.....City:

....State:

..Zipcode:

Resulting SQL:

SELECT * FROM Contacts WHERE Name LIKE 'Rob%'

In standard SQL, the percent sign functions like a wildcard in a LIKE clause. In this case, the query-by-examplme form processing software would translate the asterisk to a percent sign. (An asterisk is a more common wildcard convention outside of SQL, so here the form is attempting to be more user friendly.)

WARNING: Query-by-example software should be careful to avoid SQL injection. Otherwise, devious users may penetrate further into the database than intended by builders of the query forms.

• CRUD
• Microsoft Query by Example
• GraphQL a QBE for JSON front-ends.
• QBIC

{{refbegin}}

• {{Citation | first = Moshé M. | last = Zloof | contribution = Query by Example | title = National Computer Conference | publisher = AFIPS Press | series = AFIPS Conference Proceedings | place = Anaheim, California | volume = 44 |date=May 1975 |pages=431–8 |issn=0095-6880}}.
• {{Citation | author-link = Raghu Ramakrishnan | first1 = Raghu | last1 = Ramakrishnan | author2-link = Johannes Gehrke | first2 = Johannes | last2 = Gehrke | title = Database Management Systems | edition = 3rd | chapter-url = http://www.cs.wisc.edu/~dbbook/openAccess/thirdEdition/qbe.pdf | chapter = 6. QBE | publisher = Wisc}}.
• {{citation | first=Christopher ‘Chris’ J. | last= Date | year= 2004 | title= An Introduction to Database Systems | chapter= 8. Relational Calculus | publisher= Addison-Wesley Pearson | isbn = 978-0-321-18956-1 | pages = 242–7}}.
• {{Citation | title = Oracle Definitions | url = http://searchoracle.techtarget.com/sDefinition/0,,sid41_gci214554,00.html | publisher = Tech target}}.
• {{Citation | chapter-url = http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter5/node2.html | last = Zaiane |first=O. | title = CC 354 notes |date=1998 | chapter = 5. Query-by-Example (QBE) | publisher = SFI | place = Canada}}.

{{refend}}

• {{Cite web |url= http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-examples |title= Query by Example in Java using Hibernate |publisher= JBoss }}
• {{Cite journal |url= http://www.ahmetsoylu.com/wp-content/uploads/soylu_UAIS_2016.pdf |title= Experiencing OptiqueVQS: a multi-paradigm and ontology-based visual query system for end users |first1= Ahmet |last1= Soylu |first2= Martin |last2= Giese |first3= Ernesto |last3= Jimenez-Ruiz |first4= Guillermo |last4= Vega-Gorgojo |first5= Ian |last5= Horrocks |journal= Universal Access in the Information Society |date= 2015 |volume= 15 |pages= 129–152 |quote= Visual Query System (VQS) |doi= 10.1007/s10209-015-0404-5 |hdl= 11250/2388519 |s2cid= 254169347 }}

Category:Relational model

Category:Query languages

Category:Human–computer interaction

Category:1970s software

Category:IBM software