如果字符串与sql不匹配，如何返回0

Question

我有这些表：

School
|---------------------|------------------|------------------|
|      SchoolID       |     City         |     State        |
|---------------------|------------------|------------------|
|          1          |       City1      |       State1     |
|---------------------|------------------|------------------|
|          2          |       City2      |       State2     |
|---------------------|------------------|------------------|
|          3          |       City3      |       State3     |
|---------------------|------------------|------------------|

Teacher
|---------------------|------------------|
|       Location      |     TeacherID    |
|---------------------|------------------|
|     City1 State1    |       123-A      |
|---------------------|------------------|
|     City2 State2    |       123-B      |
|---------------------|------------------|
|     City4 State4    |       123-C      |
|---------------------|------------------|

因此，我试图通过组合学校表中的城市和州，并检查哪个学校ID链接到哪个教师ID来返回这样的查询：

|---------------------|------------------|
|      TeacherID      |     SchoolID     |
|---------------------|------------------|
|       123-A         |       1          |
|---------------------|------------------|
|       123-B         |       2          |
|---------------------|------------------|
|       123-C         |       0          |
|---------------------|------------------|

这是我到目前为止的代码

SELECT Teacher.TeacherID as TeacherID,
(
SELECT School.SchoolID
FROM School
WHERE CONCAT(School.City, ' ', School.State) = Teacher.Location
) AS SchoolID
FROM Teacher

然而，我得到的却是：

|---------------------|------------------|
|      TeacherID      |     SchoolID     |
|---------------------|------------------|
|       123-A         |       1          |
|---------------------|------------------|
|       123-B         |       2          |
|---------------------|------------------|
|       123-C         |     null         |
|---------------------|------------------|

所以我的问题是，当找不到查询时，如何将null替换为0？我尝试使用：

SELECT IFNULL(School.SchoolID, 0)

但它只是返回相同的东西。

Answer 1

您必须封装查询

SELECT Teacher.TeacherID as TeacherID,
IFNULL((
SELECT School.SchoolID
FROM School
WHERE CONCAT(School.City, ' ', School.State) = Teacher.Location
),0) AS SchoolID
FROM Teacher;

Answer 2

您需要将IFNULL应用于子选择的结果，而不是在子选择中：

IFNULL((
SELECT School.SchoolID
FROM School
WHERE CONCAT(School.City, ' ', School.State) = Teacher.Location
),0) AS SchoolID

Answer 3

我认为使用left join更容易

select t.TeacherID, ifnull(s.SchoolID,0)
from Teacher t
left join School s on concat(s.City, ' ', s.State) = t.Location