Removing duplicate rows from tables

The most efficient way to remove duplicate rows from a table is by using what we database freaks call 'CTAS'. It is an exotic acronym for the SQL statement 'CREATE TABLE AS'. Imagine table T1 contains duplicate rows and we want to create table T2 as a non-duplicate version of T1. The syntax is as follows:

CREATE TABLE T1 AS SELECT DISTINCT * FROM T2;

In Oracle this will generate lots of temp information so be sure you provide an ample temp tablespace!

Now imagine the following scenario: We have two similarly structured tables, T1 and T2, and both of them have duplicate data and we want to create a table T3 from them which will contain no duplicates. This can be achieved by using the UNION statement - believe me, this is a very powerful and indispensable SQL statement! The syntax will look something like this:

CREATE TABLE T3 AS SELECT * FROM T1 UNION SELECT * FROM T2;

Notice that I don't need to SELECT DISTINCT when using UNION statements.