join left on多张表_SQL 语句，JOIN多张表联表查询问题

非常感谢大神们的回答，三种方法都可用，这里做个总结：

方法1：

SELECT

`subject`.`sid`,

( SELECT COUNT( `course`.`id` ) FROM `course` WHERE `course`.`sid` = `subject`.`sid` ) AS `course_count`,

( SELECT COUNT( `book`.`id` ) FROM `book` WHERE `book`.`sid` = `subject`.`sid` ) AS `book_count`

FROM

`subject`;

执行计划：

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

PRIMARY

subject

index

sid

75

14

Using index

3

DEPENDENT SUBQUERY

book

ref

idx_sid

idx_sid

75

subject.sid

106

Using index

2

DEPENDENT SUBQUERY

course

ref

idx_sid

idx_sid

75

subject.sid

88

Using index

方法2

SELECT

`sid`,

SUM( `course_count` ) AS `course_count`,

SUM( `book_count` ) AS `book_count`

FROM

(

SELECT

`subject`.`sid`,

COUNT( `course`.`id` ) AS `course_count`,

0 AS `book_count`,

0 AS `article_count`

FROM

`subject`

LEFT JOIN `course` ON `subject`.`sid` = `course`.`sid`

GROUP BY

`subject`.`sid` UNION ALL

SELECT

`subject`.sid,

0 AS `course_count`,

COUNT( `book`.`id` ) AS `book_count`,

0 AS `article_count`

FROM

`subject`

LEFT JOIN `book` ON `subject`.`sid` = `book`.`sid`

GROUP BY

`subject`.`sid`

) `A`

GROUP BY

`sid`

执行计划：

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

PRIMARY

ALL

2716

Using temporary; Using filesort

2

DERIVED

subject

index

PRIMARY

75

14

Using index

2

DERIVED

course

ref

idx_sid

idx_sid

75

subject.sid

88

Using index

3

UNION

subject

index

PRIMARY

75

14

Using index

3

UNION

book

ref

idx_sid

idx_sid

75

subject.sid

106

Using index

UNION RESULT

ALL

方法3

SELECT

`c`.`sid`,

`c`.`course_count` AS `course_count`,

COUNT( `book`.`sid` ) AS `book_count`

FROM

(

SELECT

`subject`.`sid`,

COUNT( `course`.`id` ) AS `course_count`

FROM

`subject`

LEFT JOIN `course` ON `subject`.`sid` = `course`.`sid`

GROUP BY

`subject`.`sid`

) `c`

LEFT JOIN `book` ON `c`.`sid` = `book`.`sid`

GROUP BY

`c`.`sid`

执行计划：

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

PRIMARY

ALL

1232

Using temporary; Using filesort

1

PRIMARY

book

ref

idx_sid

idx_sid

75

c.sid

106

Using index

2

DERIVED

subject

index

PRIMARY

75

14

Using index

2

DERIVED

course

ref

idx_sid

idx_sid

75

subject.sid

88

Using index