SQL Crib Sheet

¹Select similar records, i.e. all records which have duplicate field1 > and field2 in a table but with different field3 (i.e. specifying which fields > must be the same and which different): >

select * from
> 
table
> 
as A,
> 
table
> 
as B
>

where A.
>
field1
>
=B.
>
field1
>

and A.
>
field2
>
=B.
>
field2
>

and A.
>
field3
>
<>B.
>
field3
>
;
>

Note: >

• It is important to specify at least one field which is different > between the two records otherwise this query will list a record as being > the same as itself. >
• This query will not find duplicate records, i.e. records with every > field the same. >

Select all records from a table which do not share a common ID with records > from a second table: >

select * from
> 
table1
>

where
> 
field1
> 
not in (select
> 
field2
> 
from
> 
table2
>
)
>

Note: >

• Sub-queries are quite slow. >
• Sub-queries are not supported in versions of MySQL prior to MySQL > 5, so the above will not work on older versions of MySQL. My thanks > to Kevin Bowman for pointing out that MySQL 5 supports sub-queries. >

An alternative using a join (which can be much faster): >

select > table1 > .* from > table1 >
left join > table2 > on ( > table1 > . > field1 > = > table2 > . > field2 > ) >
where > table2 > . > field2 > is null; >

The following method (which has been suggested by Michael Miller) is > to use EXISTS. It is much faster on SQL Server than the above (but Michael > says it is comparable with the left join technique on Oracle): >

select * from > table1 >
where not exists (select > field2 > from > table2 > where > table2.field2 > = > table1.field1 > ) >

To perform a two way join: >

select * from >
table1 > left join > table2 > on ( > table1 > . > field1 > = > table2 > . > field1 > ), >
table1 > left join > table3 > on ( > table1 > . > field2 > = > table3 > . > field3 > ) >

this has been tested on SQL Server, but not on Oracle or MySql. It does > not work with MS-Access. >

To combine the results of two queries (be aware that the number and types > of fields in b >oth queries must agree):

Highlighted by rakeshetty

Select similar records, i.e. all records which have duplicate field1 and field2 in a table but with different field3 (i.e. specifying which fields must be the same and which different):

select * from table as A, table as B
where A.field1=B.field1
and A.field2=B.field2
and A.field3<>B.field3;

Note:

• It is important to specify at least one field which is different between the two records otherwise this query will list a record as being the same as itself.
• This query will not find duplicate records, i.e. records with every field the same.

Select all records from a table which do not share a common ID with records from a second table:

select * from table1
where field1 not in (select field2 from table2)

Note:

• Sub-queries are quite slow.
• Sub-queries are not supported in versions of MySQL prior to MySQL 5, so the above will not work on older versions of MySQL. My thanks to Kevin Bowman for pointing out that MySQL 5 supports sub-queries.

An alternative using a join (which can be much faster):

select table1.* from table1
left join table2 on (table1.field1 = table2.field2)
where table2.field2 is null;

The following method (which has been suggested by Michael Miller) is to use EXISTS. It is much faster on SQL Server than the above (but Michael says it is comparable with the left join technique on Oracle):

select * from table1
where not exists (select field2 from table2 where table2.field2 = table1.field1)

To perform a two way join:

select * from
table1 left join table2 on (table1.field1 = table2.field1),
table1 left join table3 on (table1.field2 = table3.field3)

this has been tested on SQL Server, but not on Oracle or MySql. It does not work with MS-Access.

To combine the results of two queries (be aware that the number and types of fields in b

Highlighted by rakeshetty