Se utiliza en SQL-SERVER para realizar consultas de unión. Supongamos que tenemos una tabla con todos los autores y otra con todos los libros. Si deseáramos obtener un listado combinar ambas tablas de tal forma que cada autor apareciera junto a cada título, utilizaríamos la siguiente sintaxis:
SELECT
Autores.Nombre, Libros.Titulo
FROM
Autores CROSS JOIN Libros

SELF JOIN es una técnica empleada para conseguir el producto cartesiano de una tabla consigo misma. Su utilización no es muy frecuente, pero pongamos algún ejemplo de su utilización.
Supongamos la siguiente tabla (El campo autor es numérico, aunque para ilustrar el ejemplo utilice el nombre):

Queremos obtener, para cada libro, parejas de autores:
SELECT
A.Codigo, A.Autor, B.Autor
FROM
Autores A, Autores B
WHERE
A.Codigo = B.Codigo

El resultado es el siguiente: