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_Name  Sales  Txn_Date 
 Los Angeles  1500  Jan-05-1999 
 San Diego  250  Jan-07-1999 
 Los Angeles  300  Jan-08-1999 
 Boston  700  Jan-08-1999 

Table Geography

 Region_Name  Store_Name 
 East  Boston 
 East  New York 
 West  Los Angeles 
 West  San Diego 

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:

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

This page was last updated on June 19, 2023.




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