Union two tables with a table _name column

Question

I want to union two tables(Student1, Student2).

1 - Student1

| student_code |  name   |  
--------------------------
| 1            |  katia  |   
| 2            |  roger  |   
| 3            |  ken    | 

2 - Student2

| student_code |  name   |  
--------------------------
| 3            |  katia  |   
| 4            |  roger  |   
| 5            |  ken    | 

then I want get result like this.

result

|table_name| student_code |  name   |  
-------------------------------------
|Student1  | 1            |  katia  |   
|Student1  | 2            |  roger  |   
|Student1  | 3            |  ken    | 
|Student2  | 3            |  katia  |   
|Student2  | 4            |  roger  |   
|Student2  | 5            |  ken    | 

I want to use only ANSI sql.

Answer 1

 select 'Student1' AS table_name,student_code,name from student1
 union
 select 'Student2' AS table_name,student_code,name from student2

I assume you know the difference betweenUNION and UNION ALL, union brings unique records, it is same as UNION PERFORMED ON SETS while union all will bring duplicate rows as well.

In your case, it will bring duplicates even with union because of the first column which differentiates the rows.