Reputation: 21536

MySQL how to join tables on two fields

I have two tables with date and id fields. I want to join on both fields. I tried

JOIN t2 ON CONCAT(t1.id, t1.date)=CONCAT(t2.id, t2.date)

that works, but it is very slow. is there a better way to do this?

Upvotes: 130

Answers (3)

Reputation: 2971

SELECT * 
FROM t1
JOIN t2 USING (id, date)

perhaps you'll need to use 'INNER JOIN' or 'WHERE t2.id is not null' if you want results only matching both conditions

Upvotes: 33

Reputation: 12407

JOIN t2 ON t1.id=t2.id AND t1.date=t2.date

Upvotes: 226

Reputation: 74518

JOIN t2 ON (t2.id = t1.id AND t2.date = t1.date)

Upvotes: 47