存储过程取两个表的交集

DROP PROCEDURE IF EXISTS sp_table_comparison;
DELIMITER $$

CREATE PROCEDURE sp_table_comparison(IN table_x VARCHAR(255), IN table_y VARCHAR(255))
BEGIN
    DECLARE v_sql VARCHAR(1024) DEFAULT '0';
    SET v_sql = "SELECT * FROM `";
    SET v_sql = CONCAT(v_sql,table_x, "` JOIN `",table_y,"` USING(id);");
    SET @lastdata = v_sql;
    PREPARE lastdata FROM @lastdata;
    EXECUTE lastdata;
END $$
DELIMITER ;

-- 示例调用存储过程
CALL sp_table_comparison('New_Table', 'New_Table1');

– MySQL 提供了一些函数，用于将两个表合并成一个结果集。以下是一些常用的函数：
– UNION：将两个 SELECT 语句的结果合并成一个结果集。
– 示例：SELECT name FROM students UNION SELECT name FROM teachers; 结果为 “John”, “Jane”, “Mary”, “Tom”, “Jerry”
– SELECT * FROM New_Table UNION SELECT * FROM New_Table1;
– UNION ALL：与 UNION 类似，但不会去除重复的记录。
– 示例：SELECT name FROM students UNION ALL SELECT name FROM teachers; 结果为 “John”, “Jane”, “Mary”, “Tom”, “Jerry”, “John”, “Tom”
– INNER JOIN：返回两个表中满足连接条件的记录。
– 示例：SELECT * FROM students INNER JOIN teachers ON students.teacher_id = teachers.id; 结果为 “John”, “Jane”, “Mary”, “Tom”, “Jerry”
– LEFT JOIN：返回左表中的所有记录，以及右表中满足连接条件的记录。如果右表中没有匹配的记录，则用 NULL 填充。
– 示例：SELECT * FROM students LEFT JOIN teachers ON students.teacher_id = teachers.id; 结果为 “John”, “Jane”, “Mary”, “Tom”, “Jerry”, NULL, NULL, NULL, NULL, NULL
– RIGHT JOIN：返回右表中的所有记录，以及左表中满足连接条件的记录。如果左表中没有匹配的记录，则用 NULL 填充。
– 示例：SELECT * FROM students RIGHT JOIN teachers ON students.teacher_id = teachers.id; 结果为 NULL, NULL, NULL, NULL, NULL, “John”, “Jane”, “Mary”, “Tom”, “Jerry”
– FULL JOIN：返回两个表中满足连接条件的记录，以及左表和右表中没有匹配的记录。如果某个表中没有匹配的记录，则用 NULL 填充。
– 示例：SELECT * FROM students FULL JOIN teachers ON students.teacher_id = teachers.id; 结果为 “John”, “Jane”, “Mary”, “Tom”, “Jerry”, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL