self-join: table joined with itself. 
If need to compare rows to other rows in the same table. 
i.e. for each row, compare it to all others in the same table for a match.
or form all pairs of rows for some processing.
(primary to foreign key not used).
 need table alias[es].

#"Cartesian product": M*M rows
select *               from pet as p1,pet as p2;    # Must have table aliases.
select p1.name,p2.name from pet as p1,pet as p2;

#name "pairs": xy but not yx, xx, yy   "M choose 2"=(M^2-M)/2=M*(M-1)/2 pairs
#i.e. all pairs of rows, but each pair only once and no "self-pairs".
#use primary key or other unique orderable column
Select p1.name,p2.name  From pet as p1,pet as p2  Where p1.name<p2.name;
Select p1.name,p2.name  From pet as p1,pet as p2  Where p1.name<p2.name Order By p1.name;

#Or, using the pet table that has an auto_increment ID primary key field:
... Where p1.id<p2.id

#species pairs:
select p1.name,p1.species,p2.name
 from pet as p1,pet as p2
 where  p1.species=p2.species and p1.name<p2.name;

#breeding pairs (same species, different sex)
SELECT p1.name,p1.species,p1.sex,p2.name,p2.species,p2.sex
 FROM pet AS p1, pet AS p2
 WHERE p1.species=p2.species AND p1.sex='m' AND p2.sex='f';

update pet set cuteness=15 where name='claws';   
#pets of equal cuteness:
Select p1.name,p2.name,p1.cuteness 
  From pet as p1,pet as p2 
  Where p1.name<p2.name and p1.cuteness=p2.cuteness;

#fighting pairs (within 30% of each other's weight)
select p1.name,p1.weight,p2.name,p2.weight
 from pet as p1,pet as p2
 where  p1.weight between .7*p2.weight and 1.3*p2.weight and
       p2.weight between .7*p1.weight and 1.3*p1.weight and
       p1.name<p2.name;

#alphabetical rank order of name
Select p1.name,count(*) as 'alphabetic rank' 
  From pet as p1,pet as p2 
  Where p1.name>=p2.name group by p1.name;

#in order by weight  with rank shown too
Select p1.name,count(*) as 'weight rank' 
  From pet as p1,pet as p2 
  Where p1.weight>=p2.weight  Group by p1.name  Order by `weight rank`;
#Or, to include a derived column that is the ordered ranking:
set @c=0;
select name,weight,@c:=@c+1 as rank from pet order by weight;
#Or, to find a particular's rank:
set @fluffyweight=(select weight from pet where name='fluffy');
select count(*) from pet where weight<=@fluffyweight;

#pairs per owner:
select p1.owner,p1.name,p2.name 
 from pet as p1,pet as p2 
 where p2.name<p1.name and p1.owner=p2.owner 
 order by p1.owner;

#number of pairs per owner:
select p1.owner,count(*) 
 from pet as p1,pet as p2 
 where p2.name<p1.name and p1.owner=p2.owner 
 group by p1.owner;


#(can be done manually, or using variable):
#countries with same independance year as Qatar:
SELECT t1.IndepYear,t1.Name,t2.Name
 FROM Country AS t1, Country AS t2
 WHERE t1.Name='qatar' AND t1.IndepYear=t2.IndepYear;

#South American countries whose surface area is greater than Paraguay's:
select c1.Name, c1.SurfaceArea  From Country as c1, Country as c2 
 where c2.Name='paraguay' and c1.SurfaceArea>=c2.SurfaceArea and c1.Continent='South America' 
 order by SurfaceArea;