when to use 'select 1 from table'?

select 1 from table

will return a column of 1's for every row in the table. Use it with a where statement to check for an entry for a given key:

if exists(select 1 from table where some_column = 'some_value')

Eg: 

Consider the tables and data given 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);

Write a SQL query to find the products which having sales?

select product_name from products where exists (SELECT 1 FROM SALES WHERE SALES.PRODUCT_ID = PRODUCTS.PRODUCT_ID);

Write a SQL query to find the products which does not have sales at all?

select product_name from products where not exists (SELECT 1 FROM SALES WHERE SALES.PRODUCT_ID = PRODUCTS.PRODUCT_ID);