SQL > 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

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles300 Jan-08-1999
Boston700Jan-08-1999

Table Geography

Region_NameStore_Name
EastBoston
EastNew York
WestLos Angeles
WestSan Diego

and we want to find out 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:

SELECT A1.Region_Name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.Store_Name = A2.Store_Name
GROUP BY A1.Region_Name;

Result:

REGION   SALES
East   700
West   2050

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

SELECT Geography.Region_Name REGION, SUM(Store_Information.Sales) SALES

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,

SELECT A1.Region_Name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
JOIN Store_Information A2
ON A1.Store_Name = A2.Store_Name
GROUP BY A1.Region_Name;

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

The following sections explain each JOIN type in detail.

Next: SQL Inner Join




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact