SQL Join

Now we want to look at joins. To do joins correctly in SQL requires many of the elements we have introduced so far. Let's assume that we have the following two tables,

Table Store_Information

Table Geography

and we want to find sales by region. We see that table Geography includes information on regions and stores, and table Store_Information contains sales information for each store. To get the sales information by region, we have to combine the information from the two tables. Examining the two tables, we find that they are linked via the common field, "Store_Name". We will first present the SQL statement and explain the use of each segment later:

Result:

The first two lines tell SQL to select two fields, the first one is the field "Region_Name" from table Geography (aliased as REGION), and the second one is the sum of the field "Sales" from table Store_Information (aliased as SALES). Notice how the table aliases are used here: Geography is aliased as A1, and Store_Information is aliased as A2. Without the aliasing, the first line would become

which is much more cumbersome. In essence, table aliases make the entire SQL statement easier to understand, especially when multiple tables are included.

An alternative way to specify a join between tables is to use the JOIN and ON keywords. In the current example, the SQL query would be,

Several different types of joins can be performed in SQL. The key ones are as follows:

• Inner Join
• Outer Join
• Left Outer Join
• Cross Join

The following sections explain each JOIN type in detail.