Nested Query (based upon same table)

Question

I have a table of historical stock prices for each stock trading on the S&P500 index.

Each business day, 500 new records are inserted into the historical_prices table:

symbol, close_date, close_price.

The process frequently fails for numerous reasons, only a fraction of the records where added.

Thursday ran correctly (500 new records), Friday failed (adding only 400 records).

I need to identify the remaining 100 records that were not added, to rerun and complete the process.

Would a nested query be the most efficient way? Select the 500 records from Thursday, and measure against the 400 records from Friday)

SELECT * FROM historical_prices 
WHERE `close_date` = '2016-01-16'

RIGHT JOIN (
   SELECT * FROM historical_prices 
   WHERE `close_date` = '2016-01-15')
WHERE `symbol` IS NULL;

Thanks in advanced!

score 0 · Answer 1 · edited Jan 18 '16 at 02:37

0

You can do it like this:

SELECT * FROM historical_prices 
WHERE `close_date` = '2016-01-16'
AND `symbol` NOT IN (
  SELECT `symbol` FROM historical_prices 
  WHERE `close_date` = '2016-01-15'
)

edited Jan 18 '16 at 02:37

shellnhouse

21
4

answered Jan 18 '16 at 02:11

cFreed

4,404
1
23
33

score 0 · Answer 2 · answered Jan 18 '16 at 02:58

In this case, a subquery NOT IN will be easy to understand. A subquery NOT EXISTS will be a bit faster.

SELECT * FROM historical_prices h1
WHERE h1.`close_date` = '2016-01-16'
AND NOT EXISTS (
  SELECT 1 FROM historical_prices h2
  WHERE h2.`close_date` = '2016-01-15' and h2.`symbol = h1.`symbol`
)

Nested Query (based upon same table)

2 Answers2