It is possible that we did not have some of the data when we create a record. For instance we may not have the birth date, or gender. Now we want to find all of the records where the gender or birthdate is missing;
SELECT * FROM Patients WHERE Gender IS NULL OR birthdate IS NULL
We have a table called patients and another table called Diseases. We want to know what diseases each patient has. Notice that Patients has as its primary key a field called Patient that is an integer with auto increment. If you enter the query SHOW COLUMNS FROM Diseases you will see the output as shown below:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| ID | int(11) | NO | PRI | 0 | |
| disease | varchar(30) | NO | PRI |
Count=2
There are a few ways to find out which disease belong to each person.
SELECT * FROM Patients LEFT JOIN Diseases ON Patients.patient=Diseases.ID will show all of the Patients with a separate row for each disease they have.
SELECT * FROM Patients,Diseases WHERE Patients.patient=Diseases.ID will show only those patients who have a disease. There will be a separate row for each disease.
SELECT * FROM Patients,Diseases WHERE Patients.patient=Diseases.ID AND disease="cancer" Will show all of the patients who have cancer.
SELECT * FROM Patients LEFT JOIN Diseases ON Patients.patient=Diseases.ID WHERE disease="cancer" Will also show all of the patients who have cancer.