mysql not EXISTS 函数用法

最近用到了函数not EXISTS的用法,研究了好半天，稍微跟大家讲解一下。

	SELECT
	* 
FROM
	student a 
	WHERE-- 	a.id = 2 
-- 	AND
	1 = 1 
	AND NOT EXISTS (
	SELECT
		* 
	FROM
		USER b 
	WHERE
	
		a.id = b.id 
)

	
	SELECT
	* 
FROM
	student a 
	WHERE-- 	a.id = 2 
-- 	AND
	1 = 1 
	AND NOT EXISTS (
	SELECT
		* 
	FROM
		USER b 
	WHERE
	b.id != 1
	and
		a.id = b.id 
)

SELECT
	* 
FROM
	student a 
	WHERE-- 	a.id = 2 
-- 	AND
	1 = 1 
	AND NOT EXISTS (
	SELECT
		* 
	FROM
		USER b 
	WHERE
		a.id = b.id 
	AND a.address = b.address 
	AND a.sex = b.sex)

建表语句

-- 学生表
CREATE TABLE `student` (
  `id` int NOT NULL COMMENT '主键',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '姓名',
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '地址',
  `sex` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `yangquan`.`student`(`id`, `name`, `address`, `sex`) VALUES (1, '张三', '1', '1');
INSERT INTO `yangquan`.`student`(`id`, `name`, `address`, `sex`) VALUES (2, '李四', '3', '2');


-- 用户表
CREATE TABLE `user` (
  `id` int NOT NULL COMMENT '主键',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '姓名',
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '住址',
  `sex` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `yangquan`.`user`(`id`, `name`, `address`, `sex`) VALUES (1, '张三1', '1', '2');
INSERT INTO `yangquan`.`user`(`id`, `name`, `address`, `sex`) VALUES (2, '张三1', '2', NULL);

这辈子坚持与不坚持都不可怕,怕的是独自走在坚持的道路上！！！