Thing #7 - Blog About Technology: SQL Scenarios

So the thing I've had the most fun with lately that's technology related (yeah, besides Web 2.0) has been trying new stuff in SQL.

One of these is Inner and Outer Joins. I don't think I've had to grab these guys out of the toolbox for years, but now that Synergen moved some of our Customer's fields for Closeout from the main Service Request table to Activity Tracking I've found the latter is useful because they didn't migrate all of the records. So that gives us an interesting case where we need to still do a Select on Service Request records that may or may not have associated Activity Tracking records. You can handle this with a Where clause like the following:
where sr.service_request_no = at.service_request_no (+)

Maybe not that exciting to you, but helped our users out a lot! One of the next devices I like to use for interesting Select situations is using them in "Set Theory." Using a First Select "minus" the Second Select has been a great way of identifying missing records (like the above situation) that need to be created. Another that I'm going to try next is a nested SELECT statement that shows the UNION of two tables (ex: stock and items); also looking toward using two other set operators INTERSECTION and EXCEPT.