Return only one row from the right-most table for every row in the left-most table

Question

I have two tables. I want to join them in a way that only one record in the right table is returned for each record in the left most table. I've included an example below. I'd like to avoid subqueries and temporary tables as the actual data is about 4M rows. I also don't care which record in the rightmost table is matched, as long as one or none is matched. Thanks!

table users:

------------- | id | name | ------------- | 1  | mike | | 2  | john | | 3  | bill | ------------- 

table transactions:

--------------- | uid | spent |  --------------- | 1   | 5.00  | | 1   | 5.00  | | 2   | 5.00  | | 3   | 5.00  | | 3   | 10.00 | --------------- 

expected output:

--------------------- | id | name | spent | --------------------- | 1  | mike | 5.00  | | 2  | john | 5.00  | | 3  | bill | 5.00  | --------------------- 

Answer 1

Use:

  SELECT u.id,          u.name,          MIN(t.spent) AS spent     FROM USERS u     JOIN TRANSACTIONS t ON t.uid = u.id GROUP BY u.id, u.name 

Mind that this will only return users who have at least one TRANSACTIONS record. If you want to see users who don't have supporting records as well as those who do - use:

   SELECT u.id,           u.name,           COALESCE(MIN(t.spent), 0) AS spent      FROM USERS u LEFT JOIN TRANSACTIONS t ON t.uid = u.id  GROUP BY u.id, u.name 

Answer 2

If you do not care about the particular row that you get back.

select id, name, spent from users left join transactions on users.id = transactions.uid group by id 

This will return one row per user. It will be the first matched transaction.