The Subtract Join

Subtract Join

I needed a way of selecting all primary key(s) from a certain table (Table_1) that was not used as a foreign key in a sub-table (Table_2). So I went at it with my SQL know-how to no end. It was obvious that I needed to do a little catching up. 🙂

I googled the following:

• all instances of unique id not found in sub-table
• primary key not found as foreign key in sub-table
• select all rows if not exist in sub-table
• sql tutorial
• sql join
• sql reference
• sql relational databases

…and found nothing useful. However, my research did lead me to one conclusion… what I needed was the opposite of a join. I googled for it, it as in “opposite of a join”. What I found was a very useful MS KB, http://support.microsoft.com/kb/136699.

The described “Subtract Join” was exactly what I was looking for and it works! What’s odd is of all the SQL tutorials I ran into, none of them mentioned the SUBTRACT JOIN. That makes me think there might be a better way of doing this query. Is there a better way? Am I simply not using the right keywords when I search? Anyways, if there is I couldn’t find it…