mysql进阶 &&无关联的两张表按照不同条件取值

一、需求详细描述

我有两张表，表student和表student_copy1,无任何关联关系，两张表的字段都是一样的。

CREATE TABLE `student` (
  `s` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) 
INSERT INTO `f6dos`.`student`(`s`, `id`) VALUES ('1', 1);
INSERT INTO `f6dos`.`student`(`s`, `id`) VALUES ('2', 2);
INSERT INTO `f6dos`.`student`(`s`, `id`) VALUES ('3', 3);

CREATE TABLE `student_copy1` (
  `s` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) 
INSERT INTO `f6dos`.`student_copy1`(`s`, `id`) VALUES ('4', 4);
INSERT INTO `f6dos`.`student_copy1`(`s`, `id`) VALUES ('5', 5);
INSERT INTO `f6dos`.`student_copy1`(`s`, `id`) VALUES ('6', 6);

我想去表student的id为1是s的值和student_copy1的id等于4的s的值，用一条sql表示:

二、sql

结果应该是1，4

select stu.s ,copy.s as copyS
from student stu,student_copy1 copy 
where stu.id = 1 and copy.id = 4

查询结果: