sql syntax memo

mysql> select table1.id1,table1.field1,table2.id2,table2.field2 from table1, table2;

mysql> select table1.id1,table1.field1,table2.id2,table2.field2 from table1  join table2;

mysql> select table1.id1,table1.field1,table2.id2,table2.field2 from table1 inner join table2;

The above three statements return the same result: the Cartesian product of table1 and table2

If you use outer join(left outer join or right outer join), you must specify an on sub-clause, otherwise you will get a syntax error.

mysql> select table1.id1,table1.field1,table2.id2,table2.field2 from table1 left join table2 on table1.id1=table2.id2;

mysql> select table1.id1,table1.field1,table2.id2,table2.field2 from table1 left outer join table2 on table1.id1=table2.id2;

The above two statements return the same result, i.e., left join=left outer join, right join=right outer join.

There is no “full outer join”, but there is “full join”, and if you use full join, you cannot specify an on clause. If you do that, it will complain “ERROR 1054 (42S22): Unknown column ‘table1.id2′ in ‘on clause’”. The result of full join is the Cartesian product of table1 and table2.

 

delete from tablename where …

delete * from tablename

The above two are the most common forms of the delete statement, but there is another one:

delete tablename from …

This is used when multiple tables are in the from clause.

 

The quickest way to remove duplicate rows in a table

insert into temptable select * tablename group by columnname

group by clause keeps only one record for multiple rows that have the same value for columnname.

Other methods to delete repeat rows:

delete t1 from tablename as t1 inner join tablename as t2 on t1.columnname=t2.columnname and t1.id!=t2.id

delete t1 from tablename t1 inner join (select * from tablename as t2 group by columnname having count(*)>1) as t3 on t1.columnname=t3.columnname where t1.id!=t3.id

delete from tablename where id in(select t1.id from tablename as t1,tablename as t2 where t1.columnname=t2.columnname and t1.id!=t2.id)

If there is no index created on id/columnname, it would be slow to erase the redundant rows.

 

Posted in tips of hosting