Why do developers always use the GUI tools in SQL Server? I’ve always found this irritating and just vaguely assumed it’s because they aren’t familiar with SQL syntax. But when you think about it it, it’s a genuine puzzle. Developers type code all day – really heavy code too like generics, lamda functions and extension methods. They (thankfully) scorn the Visual Studio stuff where you drag a table onto the class and it pastes in lots of code to query the table into a DataSet or something. But when they want to add a column to a table, without fail they dive into the graphical table designer. And half the time the script it generates does horrible things like copy the table to another one with the new column, delete the old table, and rename the new table. Which is fine if your users don’t care about uptime. Is ALTER TABLE ADD <column definition> really that hard? I just don’t get it.
<people who have done A>
[(AND <people who have done B>) | (OR <people who have done C>)]
[APART FROM <people who have done D>]
-- Old OR select A.PersonID from ( -- A select PersonID from ... union -- OR -- C select PersonID from ... ) AorC left join -- APART FROM ( select PersonID from ... ) D on D.PersonID = AorC.PersonID where D.PersonID is null -- Old AND select distinct main.PersonID from ( -- A select PersonID from ... ) A inner join -- AND ( -- B select PersonID from ... ) B on B.PersonID = A.PersonID left join -- APART FROM ( select PersonID from ... ) D on D.PersonID = A.PersonID where D.PersonID is null
But when I tried to write the code that can generate the SQL for any combination of those (along with all the variables controlling what each SELECT did and what was in all the optional bits of each WHERE clause) my brain started to hurt. Then I remembered reading about the (then new to me) keywords INTERSECT and EXCEPT. At the time I couldn’t see what they added but I thought I would have a play and see if they might help. They were perfect for this. Here’s the new query structure:
-- The way forward select PersonID from ( ( ( -- A select PersonID from ... ) union -- OR intersect -- AND ( -- B/C select PersonID from ... ) ) except ( -- D select PersonID from ... ) ) x
I can easily swap between between UNION and INTERSECT, and omit B, C, or D as necessary. Elegant, clean and readable – pick any 3! Sometimes it really pays to read the manual.