I have no knowledge of "set theory", so I may be missing the mark here, but these are my thoughts on what you've presented as a "problem":

How do you expect the database to know that you don't want duplicate rows if you don't tell it the rows shouldn't be duplicate? Moreover, how do you expect the database to know which columns comprise a duplicate row if you don't tell it?

Take your example,

+-------------+ +------------------+
| Products    | | Supplier_Product |
+-------------+ +------------------+
| PNO | PNAME | | SNO     | PNO    |
+-----+-------+ +---------+--------+
| P1  | Screw | | S1      | P1     |
| P1  | Screw | | S1      | P1     |
| P1  | Screw | | S1      | P2     |
| P2  | Screw | +------------------+
+-------------+
[download]

and the following example,

+--------------------+  +-------------------------------+
| Purchased_Items    |  | Package_Deliveries            |
+--------------------+  +-------------------------------+
| Date       | Item  |  | Delivery_Company | Date       |
+------------+-------+  +------------------+------------+
| 2005/12/01 | Screw |  | FedEx            | 2005/12/01 |  
| 2005/12/01 | Screw |  | FedEx            | 2005/12/01 |
| 2005/12/01 | Screw |  | FedEx            | 2005/12/02 |
| 2005/12/02 | Screw |  +-------------------------------+
+--------------------+
[download]

The two sets of tables have the same structure, but duplicate rows are valid in the second case. Three screws were purchased on December 1st, and FedEx dropped off 2 packages on December 1st. You don't know which screws belongs to which package, but one would assume that you don't need to perform that link if you structured the tables in this manner. Also, you may or may not want to use DISTINCT, depending on whether or not you want to know that 3 screws were delivered, or you just want to know that screws were delivered.


If PNO and PNAME are supposed to be unique, put a constraint on the table preventing duplicates from getting inserted in the first place. If they aren't supposed to be unique, you need to tell the database when you want unique rows returned (i.e. DISTINCT), and when you don't.