Join two tables bring mismatch values MYSQL

I have two tables with >4million records i need to make a select query with where two columns match bring both tables value on this match and then i will insert that into a 3rth table:

This is table A: (bitfinex)

This is table B: (Kraken)

I need to do a SELECT where timestamp and exchange_pair matches, as you can see in this image from table B the same timestamp can have multiple exchange_pair, what i need to do is match those with the other table columns and bring both table content

this was the query i used at once but when analyzing the results i had some mismatch values on the joins ( i did not found the relation, it was not either timestamp or the exchange pair) i wonder if my query syntax is right for the result im looking for.

INSERT IGNORE INTO spreads(timestamp,exchange_pair,platform_a,platform_b,a_bid_price,b_bid_price, high_value,spread_percentage,spread)
SELECT B.timestamp, B.exchange_pair,'bitfinex' as platform_a,'kraken' as platform_b,
 B.bid,
 K.bid_price,
 (SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)),
 (SELECT (((ABS(K.bid_price - B.bid))*100)/(SELECT IF (B.bid>K.bid_price,B.bid,K.bid_price)))),
  ABS(K.bid_price - B.bid)  AS spread
FROM bitfinex AS B
JOIN kraken AS K
 ON B.timestamp = K.timestamp
JOIN kraken AS K2
 ON B.exchange_pair = K2.exchange_pair
WHERE K.exchange_pair = B.exchange_pair AND B.timestamp = K.timestamp ;

im still confused if using inner join or just join, also i added the “Where” clause to make sure exchange pair and timestamp matches.

Answer

How about:

select b.timestamp, b.exchange_pair, b.bid, k.ask_array_price
from bitfinex b
join kraken k on k.timestamp = b.timestamp and k.exchange_pair = b.exchange_pair 

I recommend that any question about SQL queries are supplemented with scripts to enable quick and effective setup of needed tables and contained data, so that query writing can commence as soon as possible. It’s good for the sender to know how to do this, it’s good for the receiver because answering the question can begin once tables and data are in place.

I admit that in this particular case, the solution might be simple enough not to have to go through the whole process, but nonetheless.

Here is an example of how such a script could look (for this particular question):

Step 1: Create the bitfinex table

CREATE TABLE test.bitfinex (
    `timestamp` DATETIME NOT NULL,
    exchange_pair varchar(30) NULL,
    bid double NULL,
    id int auto_increment NOT NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=latin1;

Step 2: Create the kraken table

CREATE TABLE test.kraken (
    id INT auto_increment NOT NULL,
    `timestamp` DATETIME NULL,
    exchange_pair varchar(30) NULL,
    ask_array_price DOUBLE NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=latin1;

Step 3: Fill the bitfinex table with sample data (or real data for that matter)

insert into bitfinex (timestamp, exchange_pair, bid)
select *,rand() * 1000 from 
(
select '2021/3/3 9:34:55' timestamp
union all
select '2021/3/3 9:34:56'
union all
select '2021/3/3 9:34:57'
union all
select '2021/3/3 9:34:58'
) a
cross join
(
select 'ETH-USD' exchange_pair
union all
select 'BTC-USD'
union all
select 'ETH-BTC'
) b

4. Fill the kraken table with data

   insert into kraken (timestamp, exchange_pair, ask_array_price) select timestamp, exchange_pair, rand() * 1000 from bitfinex