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

拼搏现实的明天。 2022-03-10 00:43 410阅读 0赞

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

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d6bjEwNTQxNjIyMjk_size_16_color_FFFFFF_t_70][]

以下是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的执行结果如图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d6bjEwNTQxNjIyMjk_size_16_color_FFFFFF_t_70 1][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d6bjEwNTQxNjIyMjk_size_16_color_FFFFFF_t_70 2][]

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d6bjEwNTQxNjIyMjk_size_16_color_FFFFFF_t_70]: /images/20220310/bee13f0cb2534405abf81ba307247175.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d6bjEwNTQxNjIyMjk_size_16_color_FFFFFF_t_70 1]: /images/20220310/fdcd8ba860b642578c8dc43090295c9d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d6bjEwNTQxNjIyMjk_size_16_color_FFFFFF_t_70 2]: /images/20220310/98aec43c6a0f4e6f995ddefc5d288f7c.png