Why Is My Query Returning Far To Many Results?

I have a bunch of candidates, who have had one or more jobs, each with a company, using some skills. Bad ascii art follows: --------------- --

Solution 1:

You are missing a join condition between candidates and jobs, so you get a cartesian product between both tables. Also, there is a problem with the join condition on skill_names, where both columns are the same (this again generates a cartesian product).

SELECT can.* , co.*, j.*, sn.*
FROM candidates AS can
JOIN jobs AS j ON j.candidate_id = can.candidate_id --> here: missing join condition
JOIN companies AS co ON j.company_id = co.company_id
JOIN skills AS s ON s.job_id = j.job_id
JOIN skill_names AS sn ON sn.skill_id = s.skill_id  --> and here: wrong join condition

Many RDBMS would raise a syntax error on a JOIN without an ON clause (if you do want a cartesian product, you need to be explicit about it by using CROSS JOIN), but, alas, not MySQL.

---

When it comes to this question:

does it make any speed difference which order I join the tables?

No. As long as you are using inner joins (not left joins), the join order does not matter to the query planner, which will rearrange them in the order which it thinks is the more efficient.