Having started to get better at SQL I’m looking at ways to optimize, change and simply parts of my own methodologies when I use SQL. This is from MS SQL 2003/2005, as it is what I use most, but I doubt it would matter much anyway.
I’ve started to notice that you can use SQL joins for much more when joining tables then equaling one column to another. This is something I’ve found great usage for and in my view is a nice little thing to be aware of.
Normally – joins are explained something along the line of this (using Inner Join but I guess it doesn’t matter much).
Suppose you have two tables, which needs to be joined:
SELECT *
FROM TableOne AS T1
INNER JOIN TableTwo AS T2 ON T1.ColumnToCompare = T2.ColumnToCompare
This is the way many see joins and utilize them.
Now for a situation I’ve used joins for.
When you map a 1:1 parent-child relationship, and aren’t using nested SQL, then a normal way is to map the child instance with a parent foreign key.
If having a longer parent-child relationship, you could extend the child with a “path” through its parents.
For instance
TableA
Field: ID
Field: ParentID
Field: ParentPath
Very much simplified, then the upmost parent instance could have ID 1 and would have ParentPath 0 and ParentID 0 because it has no parents. The child of ID 1 would thus for example have ID 2 and thus have ParentPath ‘0,1’ and ParentID 1.
Child of ID 2 would thus for example have ID 3 and thus ParentPath ‘0,1,2’ and ParentID 2.
Now to find the parent and the parent’s parent and onwards up, you could utilize a cursor or some other sort of recursive lookup, however trying my best to avoid cursors, I started to utilize the INNER JOIN for this instead.
This will incorporate the usage of PATINDEX, which illustrates the powers of a JOIN statement.
SELECT ID
FROM TableA AS T1
INNER JOIN TableA AS T2
ON PATINDEX('%,' + CAST(T1.ID AS NVARCHAR) + ',%', ',' + T2.ParentPath + ',') > 1
What it does, is that by appending commas to the ID of the T1 of TableA and checking to see if it is located within the ParentPath of T2 of TableA (also appended with commas), you will get the parent, and the parent’s parent and so onwards up the hierarchy.
This means, that in the very simplified situation mentioned, that you’re basically looking for all instances where “,ID,” from TableA is part of the TableA “,ParentPath,”.
It would in the example in question return the ID’s 1 and 2.
You could easily get ID 3 out as well, simply by adding the T1.ID to the end of the ParentPath of T2.
Of course a string search is not the world’s fastest method, but compared to some of the alternatives (cursor for instance), it does provides benefits.
However this also was mainly to show that JOINs are not just a direct column to column matching and it opens up for many tools for using SQL to do stuff.
It's just experimenting and finding what works for each situation.