Joins

Many people refer to a database as a "relational" database. More than one table is in relation to another table. Two smaller pieces of information that make a whole when combined.

There are going to be times when you will want to take a bit of information from two (or more) tables with a certain criteria. Using an address book for example, you may have one table of friends and another table of how much money they owe you. You can set the criteria to find a specific criteria that appears in both tables and produce the results. You are "joining" the information from both tables to create a full result.

A popular join is called the INNER JOIN.

The above example will compares or joins the two tables PHONE and OWES. It will look in the column called FIRST_NAME in the PHONE table and it will look in the column called FIRST_NAME in the OWES table. If any information matches in those two columns in the seperate tables, it will be put into the results.

In total, there are 4 columns of information being looked at. The above example is printing out all of the columns just to show which information is being passed into the results. You may choose exactly which data to use and display as required for a real application.

The important part of an INNER join is that if no matches are found, no results are created.

Another form of join is called the LEFT JOIN. It behaves in the same way as the inner join, but it will produce extra results if there is a match in one table but not the other.

The comma between the table names have been replaced by the words LEFT JOIN. The word WHERE has been replaced by the word ON.

During an inner join, only records matching in both tables would be placed into the results. During a left join, all of the records of the first table will be placed into the results. If the records match in the second table, they will be added into the results otherwise a NULL result will be added.