UNION in an SQL-SELECT Produces Only Distinct Records

ID: Q102892

The information in this article applies to:

• Microsoft FoxPro for MS-DOS, versions 2.0 and 2.5

SUMMARY

When you are using the UNION clause to combine the results of two SELECT statements, the FoxPro implementation of SQL will eliminate any duplicate rows.

If you want duplicate rows to appear in the resulting output, include the ALL keyword after the UNION clause.

MORE INFORMATION

The online Help and "Language Reference" manual state the following:

   Include the UNION clause to combine the final results of one
   SELECT with the final results of another SELECT. By default,
   UNION checks the combined results and eliminates duplicate rows.
   Use the optional ALL key word to prevent UNION from eliminating
   duplicate rows from the combined results.

   For example, given table A and B, each of which may contain
   duplicate records which are to be preserved and combined into a
   single cursor, issue the following command:

      SELECT * from A ;
         UNION ALL ;
      SELECT * from B

   A and B must contain the same number of columns, and the
   corresponding columns must be of the same type.