In order to understand the joins, we are going to get the help of 2 tables.
Boston $1600 Jan-07-2011
San Diego $200 Jan-08-2011
Los Angeles $500 Jan-0-2011
Boston $800 Jan-10-2011
East New York
West San Diego
West Los Angeles
Here, the function is to get to know the sales according to the region. In the first table, we have data related to sales. In the other table, we have data about stores and regions. The connection between these tables is made by the field named as “store_name”.
The syntax is given here:
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
SQL has given command from the starting two lines about selection of fields. From the first table, ‘sales’ is selected. From the second table, “region_name” is selected. The table Geography is used as aliased A1 where as Store_Information is used as aliased A2.
If we do not use aliasing, then the syntax of very first line will be like this;
The syntax will be difficult to understand without aliasing. It makes the statements easy to understand.
Now, we have to explain the third line. It contains a statement WHERE. Here, we use joins. In this line, we check that the common fields of the two tables are interlinked with each other or not. The statement WHERE tells that we have got the right output. If we do not have a right statement of WHERE, then we will have to face a Cartesian join. Cartesian join will give back the blend of the two tables. In this case, the result will be in form of 16 rows as output.