Quick note on INNER JOIN

Consider the tables, and how they are related in a chain-like way.

Let's say that I want to list name, worktype and country. I could use code with nested for loops which is labor heavy, totally unnecessary and might even create notable delay, or I could just use JOIN.

There are three types of join, I use 2 of them quite often.

INNER JOIN retrieves only the rows that returns true. OUTER JOIN returns those it can find and adds it to the results ask requested or just leaves them empty. LEFT and RIGHT (e.g. LEFT OUTER JOIN) are used to pin-point on which side you are using the OUTER JOIN statement, and to decide which table HAS to return true, and which is the OUTER JOIN'd one.
table a LEFT OUTER JOIN table b:
This will OUTER JOIN table b, and only return fields that are true, or else leave the field(s) empty and still return the table a values.

You can find more about this on google or in a good SQL book. There are also some limitations in regards of nesting when using all three types. In this case INNER join will suit us well.

id
name
worktype
1
Dag Jonny
1
2
Jonny be good
2
id
worktype
country
1
King
1
2
Emperor
2
id
country
1
Norway
2
China
There are 2 ways you could write the query. Implicit with references or Explicit with the JOIN keyword. Consider both below.
Implicit:
SELECT a.name, b.worktype, c.country
FROM table1 a, table2 b, table3 c
WHERE a.worktype=b.id AND b.country=c.id

Explicit:
SELECT a.name, b.worktype, c.country
FROM table1 a INNER JOIN (table2 b INNER JOIN table3 c ON b.country=c.id) ON a.worktype=b.id

Green code runs first, then outer. This can be nested as long as your table chain is as far as I know, far enough most likely at least. It helps to consider each INNER JOIN as being a temporary table.

"How should I start building my query?" you might be wondering. It might not be easy by first glance to see the logics in building a JOIN query. I'll tell you how I usually do it myself.

Let's say you have 3 tables like above. What you should think of doing first is just to connect them in a chain from a to c like this:
table a INNER JOIN table b INNER JOIN table c

That's the first step. Now you need to add conditions, start with the last one first:
table a INNER JOIN (table b INNER JOIN table c ON b.somevalue = c.somevalue)

Now table b and c are related and connected. Table a is the next natural chain to link, since it's the next one to the left of those already connected:
table a INNER JOIN (table b INNER JOIN table c ON b.somevalue = c.somevalue) ON a.somevalue = b.somevalue

After perfoming the basic relation/connection between the tables, you can add extra required conditions as usual by using WHERE as normal.

The important part about JOIN is to relate the tables with proper primary/foreign keys. Then take care of further conditions afterwards in a clean way.

NOTE: If you need to use another type of join in your query somewhere, e.g. LEFT OUTER JOIN you should build your query explicitly, as the implicit way will always parse as an INNER JOIN.



Written by: Dag Jonny Nedrelid
©2007-2012 http://thronic.com

Feel free to leave a comment.
Name:
URL:
0