Just few examples:
-- selecting row with maximum date:
SELECT *
FROM SomeTable st1
JOIN (SELECT SomeID, MAX(SomeDateColumn) mxSomeDateColumn
FROM SomeTable GROUP BY SomeID) st2
ON st2.SomeID = st1.SomeID
AND st2.mxSomeDateColumn = st1.SomeDateColumn
-- selecting child rows from simple hierarchy table:
SELECT st1.*
FROM SomeTable st1
JOIN SomeTable st2
ON st2.SomeParentRecID = st1.RecID
-- joining to itself on non-id column to check if another record with the same details
SELECT st1.*
FROM SomeTable st1
JOIN SomeTable st2
ON st2.SomeDetailColumn = st1.SomeDetailColumn
WHERE st2.RecId != st1.RecId