using distinct rows for join mysql

Question

Suppose I have table A, B ID in A is unique but in table B, ID is not unique

I want to SELECT DISTINCT ID

query 1:

SELECT DISTINCT ID FROM A a LEFT JOIN B b ON a.ID = B.ID WHERE ...

query 2:

SELECT DISTINCT ID FROM A WHERE ID IN (SELECT DISTINCT ID FROM B where ...)

or

SELECT DISTINCT ID FROM A a LEFT JOIN (SELECT DISTINCT ID FROM B) b ON a.ID = B.ID WHERE ...

The end result is same but

what happens in query 1 is the space of temp table is more as multiple rows from table B will come with repeated ID

In query 2 i am able to optimize space and further processing as it will have limited rows with all distinct ID's

Isn't there any way to use DISTINCT rows from table B using join and avoiding subqueries?

Actually I have even table C which I will join with this, so I need to care for the number of rows taking part in 2nd join when taking join further with table C.

One note: result of query 1 and 2 does not have to be the same, because you are using LEFT JOIN, so query 1 will return also rows with no match in table B. — Honza Haering
i have made edits in the question, we assume we have same results based on the conditions we put — user2368055

Sorin Lascu Sorin Lascu · Accepted Answer · 2015-05-20T11:28:47

SELECT DISTINCT ID FROM A a LEFT JOIN (SELECT DISTINCT ID FROM B) b ON a.ID = B.ID WHERE ...

Is this what you want?

Edit so the answer is a bit more visible:

Since your A is unique, but B isn't you can just swap the values :

SELECT DISTINCT ID FROM B b LEFT JOIN A a on a.ID = b.ID WHERE...

using distinct rows for join mysql

1 Answers