December 19, 2002
Oracle v SQL Server, Part 3
This post currently breaks my rule of only showing answers and not problems. If I find a solution I'll add an update to the end, but I can't see one coming in a hurry.
Set operations seems especially hard in SQL Server. In Oracle, its quite simple to match on several columns at once in a sub query, e.g.;
SELECT colx
FROM tablea
WHERE (col1, col2, col3) IN
(SELECT colx, coly, colz
FROM tableb)
It would seem that this is a foreign concept to SQL Server though, as that will allow only one column at a time in the where predicate. From searching around the web it seems that the advice is to avoid sub-queries where possible and to use joins. Although this is mainly for performance reasons.
This is certainly possible in the example I included above unless the number of rows in the two tables are different and the join clause is uneven. Then the ordinality (number of rows) in the two sets of data you are joining together is different and you end up with a cartesian product (i.e. a combination of all of the possible values from both sets). Which is generally not a good thing.
For example, if table a has 100 rows and table b has 250 rows, the result set will contain 25,000 rows. Using a sub query we will only ever get a maximum of 100 rows returned, which is usually what we want. I can't quite figure out how to do this in a join, but if you know please add a comment or drop me an email.
Posted by Andy Todd at December 19, 2002 03:16 PM