Mysql INNER JOIN select only one row from second table

sql

subquery

join

performance

byNikita Barsukov·Mar 1, 2025

To ensure an INNER JOIN between two tables returns only a single row from the second table, combine the join with a subquery that selects the top row based on a specified condition, utilizing the LIMIT 1 clause. This is particularly effective when you need the latest or most relevant entry.

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN (
    SELECT *
    FROM table2
    ORDER BY some_column DESC -- The latest and greatest from table2
    LIMIT 1
) AS t2 ON t1.id = t2.matching_id

This snippet gives you a joined dataset where t1 is matched with only the latest or desired t2 entry, preventing those pesky multiple join rows.

Working with grouped data

Subquery with GROUP BY for optimized selection

Working with sets of grouped data can get messy, especially if you want to fetch the most recent payment per user. Use a GROUP BY clause with a MAX aggregate to get the latest date. Your subquery will serve as a powerful device for isolating the highest date per group.

Did you know? SQL once went to a bar and saw two tables. It couldn't help but join them!

SELECT u.*, p.payment_amount, p.payment_date
FROM users u
INNER JOIN (
   SELECT user_id, MAX(payment_date) AS latest_payment_date
   FROM payments
   GROUP BY user_id
) AS latest_payments ON u.id = latest_payments.user_id
INNER JOIN payments p ON p.user_id = u.id AND p.payment_date = latest_payments.latest_payment_date
WHERE u.package_id = 1

Efficient ranking using ROW_NUMBER()

Another sophisticated technique involves utilizing ROW_NUMBER() with windows functions. By partitioning data by user ID and ordering by date, you marry each user with their latest payment. Efficiency and correctness had a baby, and they named it ROW_NUMBER().

Laugh break - Why don't SQL developers like nature? Too many bugs.

SELECT u.*, p.*
FROM users u
LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY payment_date DESC) AS rn
    FROM payments
) AS p ON u.id = p.user_id AND p.rn = 1
WHERE u.package_id = 1

Perfecting with conditions and performance insights

Filtering for the win

Need to filter rows under certain conditions? No problem. Say you only want to return rows where a user's package equals 1 or exclude older payments. This can be implemented within your JOIN conditions or by using a NOT EXISTS clause.

Data inclusivity with LEFT JOIN

Deciding between INNER JOIN and LEFT JOIN is crucial. If you need comprehensive data where users might not have any payments, LEFT JOIN is your friend. It includes all users regardless of the existence of payments.

The art of column selection

Be mindful about selecting specific columns. This is a power move to optimize query execution by minimizing the data load. In the world of large datasets, this is a game-changer.

Subquery data ordering

When dealing with aggregated or windowed data in a subquery, you need to make sure that data is in order. This is vital for the result to be effectively used in the outer query's JOIN.

explain-codes / Sql / Mysql INNER JOIN select only one row from second table

Linked

Postgresql DISTINCT ON with different ORDER BY



How to get the latest record in each group using GROUP BY?



How to group by month using SQL Server?



Using the DISTINCT keyword causes this error: not a SELECTed expression



Sql get the last date time record

