Tay Tay - 7 months ago 20
SQL Question

Syntax for WHERE clause with AND/OR?

I'm working on a simple query that will pull a list of all patients at a clinic who either have a certain diagnosis OR have had a certain procedure done. The diagnosis and procedure codes are separate fields; patients with a diagnosis might not have had a procedure done, and patients who have had a procedure might not have a diagnosis, but I still need all the patients who have had one or the other. Here's the query I've got:

SELECT PatientID, PatientName, DateOfBirth
FROM Visit
WHERE VisitStatus = 'Complete'
AND (DiagnosisCode IN ('ABC','DEF','GHI') OR ProcedureCode IN ('123','456','789'))


And yes, both code fields have a varchar data type because certain codes use both letters and numbers.

This is a simplified version of the
WHERE
clause, since I actually have upwards of 60 diagnosis codes and about 30 procedure codes. But I just wanted to make sure my syntax was right. The query is taking absolutely forever to run, but I don't know if that's just because there's a lot of data to sift through or if there's an issue with the syntax that has gotten it stuck in something similar to an infinite loop. I don't have any syntax errors coming up. I originally just had the diagnosis codes, so my
WHERE
clause just looked like
WHERE .... AND DiagnosisCode IN ('ABC','DEF','GHI')
. It still took a while to run, but it did eventually work. But after I added the
OR
part with the procedure codes, it's just running and running and running.

Does this look like it should be running without any problem? Is my syntax correct?

Answer

You might do a little better by taking a UNION of two queries. This can help Sql Server produce a better execution plan that better hits your indexes:

SELECT PatientID, PatientName, DateOfBirth
FROM Visit
WHERE VisitStatus = 'Complete'
    AND DiagnosisCode IN ('ABC','DEF','GHI')

UNION

SELECT PatientID, PatientName, DateOfBirth
FROM Visit
WHERE VisitStatus = 'Complete'
    AND ProcedureCode IN ('123','456','789')

To gain further improvements, I'd look for these two indexes on the table:

CREATE NONCLUSTERED INDEX MyVisitIndex1
ON Visit (VisitStatus, ProcedureCode)
INCLUDE (PatientID, PatientName, DateOfBirth);
CREATE NONCLUSTERED INDEX MyVisitIndex2
ON Visit (VisitStatus, DiagnosisCode)
INCLUDE (PatientID, PatientName, DateOfBirth);