When to use self-join?

SELF JOIN is used to join a table to itself as if the table were two tables; temporarily renaming at least one table in the SQL statement.

Eg: Write a SQL query to find the products whose sales decreased in 2012 compared to 2011?

Refer data below:

CREATE TABLE PRODUCTS
(
       PRODUCT_ID     INTEGER,
       PRODUCT_NAME   VARCHAR(30)
);
CREATE TABLE SALES
(
       SALE_ID        INTEGER,
       PRODUCT_ID     INTEGER,
       YEAR           INTEGER,
       Quantity       INTEGER,
       PRICE          INTEGER
);     

INSERT INTO PRODUCTS VALUES ( 100, 'Santoor');
INSERT INTO PRODUCTS VALUES ( 200, 'Sandal');
INSERT INTO PRODUCTS VALUES ( 300, 'Lux');
INSERT INTO PRODUCTS VALUES ( 400, 'Medimix');

INSERT INTO SALES VALUES ( 1, 100, 2010, 25, 6000);
INSERT INTO SALES VALUES ( 2, 100, 2011, 16, 6000);
INSERT INTO SALES VALUES ( 3, 100, 2012, 8,  6000);
INSERT INTO SALES VALUES ( 4, 200, 2010, 10, 9000);
INSERT INTO SALES VALUES ( 5, 200, 2011, 15, 9000);
INSERT INTO SALES VALUES ( 6, 200, 2012, 20, 9000);
INSERT INTO SALES VALUES ( 7, 300, 2010, 20, 8000);
INSERT INTO SALES VALUES ( 8, 300, 2011, 18, 8000);
INSERT INTO SALES VALUES ( 9, 300, 2012, 20, 8000);

Query:

SELECT PRODUCTS.PRODUCT_NAME
FROM   PRODUCTS ,
       SALES currsales,
       SALES prevsales
WHERE  PRODUCTS.PRODUCT_ID = currsales.PRODUCT_ID
AND    currsales.YEAR = 2012
AND    prevsales.YEAR = 2011
AND    currsales.PRODUCT_ID = prevsales.PRODUCT_ID
AND   currsales.QUANTITY < prevsales.QUANTITY;