我有一堆应聘者，他们有一些或多个工作，每个人都在公司工作，并且使用了一些技能。
坏的ascii艺术如下：
--------------- --------------- | candidate 1 | | candidate 2 | --------------- \ -------------- / \ | ------- -------- etc |job 1| | job 2 | ------- --------- / \ / \ --------- --------- --------- -------- |company | | skills | |company | | skills | --------- --------- ---------- ----------
这是我的数据库：
mysql> describe jobs; +--------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+----------------+ | job_id | int(11) | NO | PRI | NULL | auto_increment | | candidate_id | int(11) | NO | MUL | NULL | | | company_id | int(11) | NO | MUL | NULL | | | start_date | date | NO | MUL | NULL | | | end_date | date | NO | MUL | NULL | | +--------------+---------+------+-----+---------+----------------+
。
mysql> describe candidates; +----------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+----------+------+-----+---------+----------------+ | candidate_id | int(11) | NO | PRI | NULL | auto_increment | | candidate_name | char(50) | NO | MUL | NULL | | | home_city | char(50) | NO | MUL | NULL | | +----------------+----------+------+-----+---------+----------------+
mysql> describe companies; +-------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------+------+-----+---------+----------------+ | company_id | int(11) | NO | PRI | NULL | auto_increment | | company_name | char(50) | NO | MUL | NULL | | | company_city | char(50) | NO | MUL | NULL | | | company_post_code | char(50) | NO | | NULL | | | latitude | decimal(11,8) | NO | | NULL | | | longitude | decimal(11,8) | NO | | NULL | | +-------------------+---------------+------+-----+---------+----------------+
mysql> describe skills; +----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+-------+ | skill_id | int(11) | NO | MUL | NULL | | | job_id | int(11) | NO | MUL | NULL | | +----------+---------+------+-----+---------+-------+
mysql> describe skill_names; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | skill_id | int(11) | NO | PRI | NULL | auto_increment | | skill_name | char(32) | NO | MUL | NULL | | +------------+----------+------+-----+---------+----------------+
这是我对查询的尝试（请注意，我打算将通配符更改为字段名称；我只是想使某些功能生效）：
SELECT can.* , co.*, j.*, sn.* FROM candidates AS can JOIN jobs AS j 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 s.skill_id = s.skill_id
HediSql说 /* Affected rows: 0 Found rows: 34,461,651 Warnings: 0 Duration for 1 query: 0.000 sec. (+ 105.078 sec. network) */
/* Affected rows: 0 Found rows: 34,461,651 Warnings: 0 Duration for 1 query: 0.000 sec. (+ 105.078 sec. network) */
查询出了什么问题？我希望从不良的ascii艺术中可以清楚地知道我要达到的目标。
（此外，它对我连接表的顺序是否有任何速度差异？我将担心新的MySql v8函数会在以后将其作为嵌套JSON进行检索）
您缺少candidates和之间jobs的联接条件，因此在两个表之间都得到了笛卡尔积。另外，在上的连接条件也存在问题skill_names，其中两列都相同（这再次生成笛卡尔积）。
candidates
jobs
skill_names
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
许多RDBMS都会在JOIN不带ON子句的a上引发语法错误（如果您确实想要笛卡尔乘积，则需要使用来明确指出它CROSS JOIN），但是，可惜的是，不是MySQL。
JOIN
ON
CROSS JOIN
当涉及到这个问题时：
加入表的顺序对速度有什么影响？
否。只要您使用inner joins（而不是left joins），连接顺序就对查询计划程序无关紧要，查询计划程序将按认为效率更高的顺序重新排列它们。
inner join
left join
