SQL的内外连接对查询数据的影响（以MySQL为例）

先来对内外连接进行一个简单的说明，想要深入了解请自行百度教程。

需要查找两张表同时存在的数据，使用内连接；
需要查找两张表中一张表存在，另一张表不存在的时候使用左外链接 或 右外链接；
内连接的查询结果都是满足连接条件的元组。但有时我们也希望输出那些不满足连接条件的元组信息。
1)左外连接（LEFTOUTER JOIN）
如果在连接查询中，连接管子左端的表中所有的元组都列出来，并且能在右端的表中找到匹配的元组，那么连接成功。
2）右外连接（RIGHTOUTERJOIN）
右外连接与左外连接类似，只是右端表中的所有元组都列出，限制左端表的数据必须满足连接条件，而不管右端表中的数据是否满足连接条件，均输出表中的内容。
3）全外连接（FULL OUTER JOIN）
全外连接查询的特点是左、右两端表中的元组都输出，如果没能找到匹配的元组，就使用NULL来代替。

最近在写一个报表的SQL语句，一开始用inner join来进行连接查询，发现写到最后没有数据了。。。。。。

以下是SQL语句

SELECT
	t1.receiving_lerk_name,
	t1.task_create_time,
	IFNULL(t1.task_count, 0) task_count,
	IFNULL(t2.task_complete_count, 0) task_complete_count,
	ROUND(
		(
			IFNULL(t2.task_complete_count, 0) / t1.task_count
		) * 100,
		2
	) complete_percent,
	IFNULL(t3.pass_count_work, 0) pass_count_work,
	IFNULL(t4.pass_count_no_work, 0) pass_count_no_work,
	IFNULL(t5.reject_count_work, 0) reject_count_work,
	IFNULL(t6.reject_count_no_work, 0) reject_count_no_work
FROM
	(
		SELECT
			receiving_lerk_name,
			DATE_FORMAT(create_time, '%Y-%m-%d') task_create_time,
			COUNT(id) task_count,
			receiving_lerk_id
		FROM
			biz_order_audit
		WHERE
			audit_count = '1' -- DATE_FORMAT(create_time,'%Y-%m-%d')
		GROUP BY
			DATE_FORMAT(create_time, '%Y-%m-%d'),
			receiving_lerk_id
	) t1
INNER JOIN (
	SELECT
		DATE_FORMAT(create_time, '%Y-%m-%d') task_create_time,
		COUNT(id) task_complete_count,
		receiving_lerk_id
	FROM
		biz_order_audit
	WHERE
		audit_count = '1' -- DATE_FORMAT(create_time,'%Y-%m-%d')
	AND end_time >= create_time
	AND end_time <= DATE_ADD(create_time, INTERVAL 24 HOUR)
	GROUP BY
		DATE_FORMAT(create_time, '%Y-%m-%d'),
		receiving_lerk_id
) t2 ON t2.receiving_lerk_id = t1.receiving_lerk_id
AND t2.task_create_time = t1.task_create_time
INNER JOIN (
	SELECT
		DATE_FORMAT(create_time, '%Y-%m-%d') task_create_time,
		receiving_lerk_id,
		SUM(pass_count) pass_count_work
	FROM
		biz_order_audit
	WHERE
		type = '1' -- DATE_FORMAT(create_time,'%Y-%m-%d')
	AND DATE_FORMAT(end_time, '%H') >= '09'
	AND DATE_FORMAT(end_time, '%H') <= '18'
	AND audit_count IN (
		SELECT
			MIN(t1.audit_count)
		FROM
			biz_order_audit t1
		WHERE
			t1.order_id = order_id -- DATE_FORMAT(t1.create_time,'%Y-%m-%d')
		AND t1.type = '1'
		AND DATE_FORMAT(t1.end_time, '%H') >= '09'
		AND DATE_FORMAT(t1.end_time, '%H') <= '18'
	)
	GROUP BY
		DATE_FORMAT(create_time, '%Y-%m-%d'),
		receiving_lerk_id
) t3 ON t3.receiving_lerk_id = t1.receiving_lerk_id
AND t3.task_create_time = t1.task_create_time
INNER JOIN (
	SELECT
		DATE_FORMAT(create_time, '%Y-%m-%d') task_create_time,
		receiving_lerk_id,
		SUM(pass_count) pass_count_no_work
	FROM
		biz_order_audit
	WHERE
		type = '1' -- DATE_FORMAT(create_time,'%Y-%m-%d')
	AND (
		(
			DATE_FORMAT(end_time, '%H') >= '00'
			AND DATE_FORMAT(end_time, '%H') <= '09'
		)
		OR (
			DATE_FORMAT(end_time, '%H') > '18'
			AND DATE_FORMAT(end_time, '%H') <= '24'
		)
	)
	AND audit_count IN (
		SELECT
			MIN(t1.audit_count)
		FROM
			biz_order_audit t1
		WHERE
			t1.order_id = order_id -- DATE_FORMAT(t1.create_time,'%Y-%m-%d')
		AND t1.type = '1'
		AND (
			(
				DATE_FORMAT(t1.end_time, '%H') >= '00'
				AND DATE_FORMAT(t1.end_time, '%H') <= '09'
			)
			OR (
				DATE_FORMAT(t1.end_time, '%H') > '18'
				AND DATE_FORMAT(t1.end_time, '%H') <= '24'
			)
		)
	)
	GROUP BY
		DATE_FORMAT(create_time, '%Y-%m-%d'),
		receiving_lerk_id
) t4 ON t4.receiving_lerk_id = t1.receiving_lerk_id
AND t4.task_create_time = t1.task_create_time
INNER JOIN (
	SELECT
		DATE_FORMAT(create_time, '%Y-%m-%d') task_create_time,
		receiving_lerk_id,
		SUM(reject_count) reject_count_work
	FROM
		biz_order_audit
	WHERE
		type = '2' -- DATE_FORMAT(create_time,'%Y-%m-%d')
	AND DATE_FORMAT(end_time, '%H') >= '09'
	AND DATE_FORMAT(end_time, '%H') <= '18'
	AND audit_count IN (
		SELECT
			t1.audit_count
		FROM
			biz_order_audit t1
		WHERE
			t1.order_id = order_id -- DATE_FORMAT(t1.create_time,'%Y-%m-%d')
		AND t1.type = '2'
		AND DATE_FORMAT(t1.end_time, '%H') >= '09'
		AND DATE_FORMAT(t1.end_time, '%H') <= '18'
		AND t1.end_time < (
			SELECT
				t3.end_time
			FROM
				biz_order_audit t3
			WHERE
				t3.type = '1'
			AND t3.id = (
				SELECT
					MIN(t4.id)
				FROM
					biz_order_audit t4
				WHERE
					t4.type = '1'
				AND t1.order_id = t4.order_id -- DATE_FORMAT(t4.create_time,'%Y-%m-%d')
			)
		)
	)
	GROUP BY
		DATE_FORMAT(create_time, '%Y-%m-%d'),
		receiving_lerk_id
) t5 ON t5.receiving_lerk_id = t1.receiving_lerk_id
AND t5.task_create_time = t1.task_create_time
INNER JOIN (
	SELECT
		DATE_FORMAT(create_time, '%Y-%m-%d') task_create_time,
		receiving_lerk_id,
		SUM(reject_count) reject_count_no_work
	FROM
		biz_order_audit
	WHERE
		type = '2' -- DATE_FORMAT(create_time,'%Y-%m-%d')
	AND (
		(
			DATE_FORMAT(end_time, '%H') >= '00'
			AND DATE_FORMAT(end_time, '%H') <= '09'
		)
		OR (
			DATE_FORMAT(end_time, '%H') > '18'
			AND DATE_FORMAT(end_time, '%H') <= '24'
		)
	)
	AND audit_count IN (
		SELECT
			t1.audit_count
		FROM
			biz_order_audit t1
		WHERE
			t1.order_id = order_id -- DATE_FORMAT(t1.create_time,'%Y-%m-%d')
		AND t1.type = '2'
		AND (
			(
				DATE_FORMAT(end_time, '%H') >= '00'
				AND DATE_FORMAT(end_time, '%H') <= '09'
			)
			OR (
				DATE_FORMAT(end_time, '%H') > '18'
				AND DATE_FORMAT(end_time, '%H') <= '24'
			)
		)
		AND t1.end_time < (
			SELECT
				t3.end_time
			FROM
				biz_order_audit t3
			WHERE
				t3.type = '1'
			AND t3.id = (
				SELECT
					MIN(t4.id)
				FROM
					biz_order_audit t4
				WHERE
					t4.type = '1'
				AND t1.order_id = t4.order_id -- DATE_FORMAT(t4.create_time,'%Y-%m-%d')
			)
		)
	)
	GROUP BY
		DATE_FORMAT(create_time, '%Y-%m-%d'),
		receiving_lerk_id
) t6 ON t6.receiving_lerk_id = t1.receiving_lerk_id
AND t6.task_create_time = t1.task_create_time

使用inner join的执行结果如图：

使用left join的执行结果如图：

在进行比较复杂的多表查询或者重复查询时，还是需要好好想一想使用哪种连接方式的

（以上纯属个人看法，不喜勿喷！如有错误，请指出！）