Re: Bags versus sets; are they needed?

Don't forget that you can select a subset of the columns in a table. So you can easily get duplicate resulsts when selecting not all columns. I agree with you that it doesn't seem very useful when a table contains duplicates when all columns are selected.

Usually a table has a unique primary index. That is the easiest way to avoid duplicates. I don't think allowing duplicate rows has anything to do with performance.

A similar difference between set theory and relational databases in practice is
that sets don't have a sequence. It would be hard to tell end-users that they cannot
rely on the sequence of the data that is presented to them. :)

Peter Koch Larsen wrote:

> Hi all
>
> It is a wellknown fact that SQL returns duplicate values unless you
> use the SELECT DISTINCT option. What is almost as wellknown (i hope!)
> is that relational theory is based on sets.
> I believe that the original motivation for returning bags ("sets" with
> duplicate values retained) was one of performance: to eliminate the
> extra step, that would be required if duplicates were to be removed.
> My question is simple: Do you personally have code, where the
> retaining of duplicate tuples is essential/convenient/whatever? If so
> - could you provide me with an insight in to what problem is solved.
>
> Kind regards
> Peter Koch Larsen

--
Anton Versteeg
DB2 Specialist
IBM Netherlands