sql示例

前期准备

CREATE DATABASE `db2021` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

-- db2021.student_club definition

CREATE TABLE `student_club` (
  `student_id` varchar(100) NOT NULL,
  `club_id` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- db2021.student_info definition

CREATE TABLE `student_info` (
  `student_id` varchar(100) NOT NULL,
  `gender` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO db2021.student_club (student_id,club_id) VALUES
	 ('1001','A01'),
	 ('1003','A01'),
	 ('1004','A02'),
	 ('1005','A02');
	 
INSERT INTO db2021.student_info (student_id,gender) VALUES
	 ('1001','male'),
	 ('1002','male'),
	 ('1003','female');

写法一

SELECT 
	sc.*, si.gender 
FROM student_club sc 
	left join 
		student_info si on sc.student_id = si.student_id ;

student_id|club_id|gender|
----------+-------+------+
1001      |A01    |male  |
1003      |A01    |female|
1004      |A02    |      |
1005      |A02    |      |

执行顺序

首先从student_club取出所有数据，然后根据on后面的条件从student_info取出符合条件的数据。

写法二

SELECT 
	sc.*, si.gender 
FROM student_club sc 
	left join 
		student_info si on sc.student_id = si.student_id and si.gender = 'female';
	
student_id|club_id|gender|
----------+-------+------+
1001      |A01    |      |
1003      |A01    |female|
1004      |A02    |      |
1005      |A02    |      |

其实 1001 的gender是有值的，但是因为条件过滤，它没有值了。

写法三

SELECT 
	sc.*, si.gender 
FROM student_club sc 
	left join 
		student_info si on sc.student_id = si.student_id 
WHERE si.gender = 'female';
	
student_id|club_id|gender|
----------+-------+------+
1003      |A01    |female|

首先从student_club取出数据，然后根据on后面的条件从student_info取出数据，根据where后面的条件对数据进行过滤。

写法四

SELECT 
	sc.*, si.gender 
FROM student_club sc 
	join 
		student_info si on sc.student_id = si.student_id ;
	
student_id|club_id|gender|
----------+-------+------+
1001      |A01    |male  |
1003      |A01    |female|

写法五

SELECT 
	sc.*, si.gender 
FROM student_club sc 
	join 
		student_info si on sc.student_id = si.student_id and si.gender = 'female';
	
student_id|club_id|gender|
----------+-------+------+
1003      |A01    |female|

写法六

SELECT 
	sc.*, si.gender 
FROM student_club sc 
	join 
		student_info si on sc.student_id = si.student_id 
WHERE si.gender = 'female';

student_id|club_id|gender|
----------+-------+------+
1003      |A01    |female|

表面上看出来的结果和写法五是一样的，

在连接表时，ON 和 WHERE 的使用场景以及性能上的区别如下：

使用场景：

ON：在SQL查询中，当使用JOIN（如INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN等）来连接两个或多个表时，ON子句用于指定连接条件。它告诉数据库如何根据这些条件将表连接在一起。这些条件通常涉及两个表中的列之间的比较。

WHERE：WHERE子句在连接表后用于过滤结果集。它可以包含任何有效的SQL条件，以限制返回的行。虽然WHERE子句也可以包含连接条件，但在大多数情况下，使用ON子句来指定连接条件会使查询更清晰和易于理解。

性能区别：

从性能的角度来看，ON和WHERE在连接表时的差异主要取决于查询优化器和执行计划。然而，一般来说，当连接条件在ON子句中指定时，数据库可能会更有效地优化查询，因为它可以在生成连接结果之前应用这些条件。

另一方面，当连接条件在WHERE子句中指定时，数据库首先会生成完整的连接结果集，然后再应用过滤条件。这可能会导致更大的中间结果集和更高的内存使用，特别是对于大型表或复杂的连接操作。

此外，当使用LEFT JOIN或RIGHT JOIN时，ON和WHERE的行为会有所不同。在LEFT JOIN中，ON子句指定的条件用于确定哪些行从右表（或“被连接”的表）包括在结果集中，而WHERE子句用于过滤结果集。如果在WHERE子句中指定了与右表相关的条件，它可能会排除那些在LEFT JOIN中应该出现的行。

总结：

使用ON子句来指定连接条件可以使查询更清晰、更易于理解，并且可能有助于数据库更有效地优化查询。

WHERE子句应该用于在连接表后过滤结果集，而不是指定连接条件（除非在隐式连接中使用）。

当使用LEFT JOIN或RIGHT JOIN时，请注意ON和WHERE在行为上的差异，以避免意外的结果。