Write a SQL query to find the products which does not have sales at all? ( Which one is efficient and why )
<p>As per the question title I would like to understand which SQL would be efficient and why based on the below solutions assuming the data volumes are high in both the tables PRODUCTS( millions of rows ) AND SALES( billions of rows ). Here are the schema details as below. I am not interested in getting the solution for the question but I would like to get better learning on the optimal solution.</p>

<pre><code>CREATE TABLE PRODUCTS
(
       PRODUCT_ID     INTEGER,
       PRODUCT_NAME   VARCHAR2(30)
);
CREATE TABLE SALES
(
       SALE_ID        INTEGER,
       PRODUCT_ID     INTEGER,
       YEAR           INTEGER,
       Quantity       INTEGER,
       PRICE          INTEGER
);       

INSERT INTO PRODUCTS VALUES ( 100, 'Nokia');
INSERT INTO PRODUCTS VALUES ( 200, 'IPhone');
INSERT INTO PRODUCTS VALUES ( 300, 'Samsung');
INSERT INTO PRODUCTS VALUES ( 400, 'LG');

INSERT INTO SALES VALUES ( 1, 100, 2010, 25, 5000);
INSERT INTO SALES VALUES ( 2, 100, 2011, 16, 5000);
INSERT INTO SALES VALUES ( 3, 100, 2012, 8,  5000);
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, 7000);
INSERT INTO SALES VALUES ( 8, 300, 2011, 18, 7000);
INSERT INTO SALES VALUES ( 9, 300, 2012, 20, 7000);
COMMIT;
</code></pre>

<p>--Solution 1</p>

<pre><code>SELECT P.PRODUCT_NAME
FROM   PRODUCTS P
       LEFT OUTER JOIN
       SALES S
ON     (P.PRODUCT_ID = S.PRODUCT_ID);
WHERE  S.QUANTITY IS NULL
</code></pre>

<p>--Solution 2</p>

<pre><code>SELECT P.PRODUCT_NAME
FROM   PRODUCTS P
WHERE  P.PRODUCT_ID NOT IN 
       (SELECT DISTINCT PRODUCT_ID FROM SALES);
</code></pre>

<p>--Solution 3  </p>

<pre><code>SELECT P.PRODUCT_NAME
FROM   PRODUCTS P
WHERE  NOT EXISTS
       (SELECT 1 FROM SALES S WHERE S.PRODUCT_ID = P.PRODUCT_ID);
</code></pre>