What is the difference between a single-row query and a multiple-row query and why is it important to know the difference?
--First, to cover the obvious, a single-row query is a query that returns one row as
its result, and a multiple-row query is a query that returns more than one row as its result. Whether a
query returns one row or more than one row is entirely dependent on the design (or schema) of the
tables of the database. As query-writer, you must be aware of the schema, be sure to include enough
conditions, and structure your SQL statement properly, so that you will get the desired result (either one
row or multiple rows). For example, if you wanted to be sure that a query of the AntiqueOwners table
returned only one row, consider an equal condition of the primary key column, OwnerID.
Three reasons immediately come to mind as to why this is important. First, getting multiple rows when
you were expecting only one, or vice-versa, may mean that the query is erroneous, that the database is
incomplete, or simply, you learned something new about your data. Second, if you are using an update
or delete statement, you had better be sure that the statement that you write performs the operation on
the desired row (or rows)...or else, you might be deleting or updating more rows than you intend. Third,
any queries written in Embedded SQL must be carefully thought out as to the number of rows returned.
If you write a single-row query, only one SQL statement may need to be performed to complete the
programming logic required. If your query, on the other hand, returns multiple rows, you will have to use
the Fetch statement, and quite probably, some sort of looping structure in your program will be required
to iterate processing on each returned row of the query.