mysql - 如何从单独的子类表中进行选择？

Question

我有一个非常简单的数据库。这个想法是，Student 和 Teacher 都继承自由 id 和 name 组成的“Person”。

create table Student (
    id int,
    name text,
    year int
);

create table Teacher (
    id int,
    name text,
    department varchar(15)
);

insert into Student (id, name, year) values
    (1, 'herper', 2007),
    (2, 'derpins', 2010);

insert into Teacher (id, name, department) values
    (3, 'skerp', 'csc');

我正在尝试创建一个将返回以下内容的选择语句:

id  name       year   department
1   'herper'   2007   NULL
2   'derpins'  2010   NULL
3   'skerp'    NULL   'csc'

这是我得到的最接近的:

select *
from Student
union
select * from Teacher;

有什么想法吗？谢谢!

Answer 1

SELECT id, name, year, NULL department
  FROM Student
 UNION
SELECT id, name, NULL, department 
  FROM Teacher;

输出:

| ID |    NAME |   YEAR | DEPARTMENT |
--------------------------------------
|  1 |  herper |   2007 |     (null) |
|  2 | derpins |   2010 |     (null) |
|  3 |   skerp | (null) |        csc |

<强> SQLFiddle