MySQL 匹配 where 子句与可选的 NULL [英] MySQL Matching where clause with optional NULL
本文介绍了MySQL 匹配 where 子句与可选的 NULL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有 2 个表 - 患者和发行者.我从患者和发行者表中提取整个患者表以及 issuer_name.可选地，可能没有患者标识符的发行者.
I have 2 tables - patients, and issuers. I with to extract entire patients table along with issuer_name from patients and issuers table. Optionally there might be no issuer of patient identifier.
如果我这样做:select * frompatients, issuer_name wherepatients.issuer_of_patient_identifier=issuer.issuer_id 不返回任何内容，以防相应的患者表行 issuer_of_patient_identifier 为 NULL.
If i do:
select * from patients, issuer_name where patients.issuer_of_patient_identifier=issuer.issuer_id doesn't return anything in case for the corresponding patient table row issuer_of_patient_identifier is NULL.
我如何做到这一点?
mysql> describe patients;
+------------------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------------+--------------+------+-----+-------------------+-----------------------------+
| patient_id | int(11) | NO | PRI | NULL | auto_increment |
| patient_identifier | varchar(64) | YES | MUL | NULL | |
| issuer_of_patient_identifier | int(11) | YES | MUL | NULL | |
| medical_record_locator | varchar(64) | YES | | NULL | |
| patient_name | varchar(128) | NO | MUL | NULL | |
| birth_date | datetime | YES | | NULL | |
| deceased_date | datetime | YES | | NULL | |
| gender | varchar(16) | YES | | NULL | |
| ethnicity | varchar(45) | YES | | NULL | |
| date_created | datetime | NO | | CURRENT_TIMESTAMP | |
| last_update_date | datetime | YES | | NULL | on update CURRENT_TIMESTAMP |
| last_updated_by | varchar(128) | NO | | NULL | |
+------------------------------+--------------+------+-----+-------------------+-----------------------------+
12 rows in set (0.00 sec)
mysql> describe issuers;
+------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+-------------------+-----------------------------+
| issuer_id | int(11) | NO | PRI | NULL | auto_increment |
| issuer_name | varchar(64) | NO | MUL | NULL | |
| issuer_uid | varchar(128) | YES | | NULL | |
| issuer_uid_type | varchar(64) | YES | | NULL | |
| date_created | datetime | NO | | CURRENT_TIMESTAMP | |
| last_update_date | datetime | YES | | NULL | on update CURRENT_TIMESTAMP |
| last_updated_by | varchar(128) | NO | | NULL | |
+------------------+--------------+------+-----+-------------------+-----------------------------+
推荐答案
查询是
select * from patients
LEFT JOIN issuer_name ON
patients.issuer_of_patient_identifier = issuer.issuer_id
无连接:
select * from patients, issuer_name
where patients.issuer_of_patient_identifier = issuer.issuer_id
OR patients.issuer_of_patient_identifier IS NULL
这篇关于MySQL 匹配 where 子句与可选的 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
