How to JOIN without duplicate rows or JOIN subquery

Question

I have 2 tables, transactions and identifiers. Each transaction has a user_id, and each user_id can have multiple identifiers e.g.

transactions

user_id    |    amount     |    timestamp
12              10.00           1234567890
17              5.00            1234567890
12              7.00            1234567890 
3               2.50            1234567890

identifiers

identifier     |     user_id
AEFT67                12
JHDASJK               12
KJSIDJ6               3
LKSDLK                5
HSDJH8                17
IUSDI5                17

I'd like to get a result like this:

result:

user_id      |     identifier       |       amount      |     timestamp
12                  AEFT67                  10.00            1234567890
17                  HSDJH8                   5.00            1234567890
12                  AEFT67                   7.00            1234567890
3                   KJSIDJ6                  2.50            1234567890

Both tables have many millions of rows and its important that I don't get duplicates when using a join (otherwise the amounts will be wrong).

I originally tried:

SELECT t.user_id, t.amount, i.identifier
      FROM transactions AS t 
      LEFT JOIN identifiers AS i ON i.id = (
        SELECT
          i2.id
        FROM identifiers i2 
        WHERE i2.user_id = t.user_id
        LIMIT 1
      )
WHERE t.timestamp BETWEEN 1234567890 AND 1234567890

Note - I don't actually mind which identifer I get for a user, but a user could have many. The nested JOIN is very slow (about 40sec) though on large datasets so I tried:

SELECT t1.user_id, t1.amount, i1.identifier FROM
    (SELECT *
      FROM transactions as t
    WHERE t.timestamp BETWEEN 1234567890 AND 1234567890) as t1
LEFT JOIN
    (SELECT * FROM identifiers GROUP BY user_id) i1
    ON i1.user_id =t1.user_id

This actually cut the times down in half, but still very slow.

I feel like I'm missing something obvious, in each case I am searching through a lot of data in the identifiers table which is slowing it down (millions of rows rather than the 1000 or so needed). I feel that if I was able to use the result of the first query as a parameter in the second it would be much faster i.e.:

SELECT * FROM
    (SELECT *
      FROM transactions 
    WHERE t.timestamp BETWEEN 1234567890 AND 1234567890) as t1
LEFT JOIN
    (SELECT * FROM identifiers WHERE user_id in (t1.user_id))

Is there a better way to get my filtered transactions with a reference to a single (any) identifier?

EDIT: Here's an sql fiddle set up: http://sqlfiddle.com/#!9/ecad23/6

EDIT2: For further clarification, I need to maintain a record of every single transaction, so the number of rows returned should be exactly what you'd expect if the where query was only applied to transactions table. Users can have multiple transactions so grouping a final result won't work

If I understand you correctly, you want to pull a single identifier per user. Do you have any criteria about how the identifier is selected (alphabetical order, string size) or is any one that matches fine? — dragmosh
@dragmosh no criteria needed for the identifier, any would do — contool

ulferts ulferts · Accepted Answer · 2017-09-28T18:12:11

Well the simple query that does what you want is:

SELECT 
  t.user_id
  , amount
  , timestamp
  , identifier
FROM 
  transactions AS t 
LEFT JOIN identifiers AS i 
  ON i.user_id = t.user_id
WHERE 
  t.timestamp BETWEEN 1234567890 AND 1234567890  
GROUP BY 
  t.user_id
  , amount
  , timestamp

As the query should be fairly easy to execute and optimize by the DBMS I'd guess that you are missing indexes on some column if it isn't fast.

At its core it is a simple join of two tables. One could probably swap LEFT JOIN for JOIN without any changes in the results if it is ensured, that the consistency of the data is intact, meaning that there always be a user for every transaction.

The duplicates generated by the join are removed again by the GROUP BY. There is no aggregate function on the identifier so MySql will simply pick one. This might break if the ONLY_FULL_GROUP_BY flag is active which would require us to have the identifier in an aggregate function. As identifier is a varchar one cannot simply use one like MIN or MAX. But if the flag is not set, there is no problem.

Correction Actually I tried and it also seems to be possible to use e.g. MAX on a varchar. I didn't know that.

How to JOIN without duplicate rows or JOIN subquery

2 Answers