In today’s issue I want to explain the different options of combining data of multiple tables (Joins) in Oracle. There are several options, the following are the most common ones:
- Inner join / equi join (this article)
- Left/right outer join
- Cross Join / cross product
Let’s say you have two tables with data:
| ID | Name | Age |
| 1 | Gaussling | 32 |
| 2 | Smith | 45 |
| 3 | Meier | 25 |
Table1: TBL_CUSTOMER
| Date | Customer_ID | Turnover |
| 2013-12-01 | 1 | 100 € |
| 2013-12-02 | 1 | 50€ |
| 2013-12-02 | 2 | 200€ |
| 2013-12-03 | 3 | 75€ |
Table2: TBL_SALES
The most common case is the inner join. In the above example we maybe want to know how much turnover we made with each customer. For this we can use an inner join:
SELECT c.Name, SUM(t.Turnover)
FROM TBL_CUSTOMER c JOIN TBL_SALES t ON c.ID=t.Customer_ID
GROUP BY c.Name
The database now would go through the TBL_SALES table and for each row it would check in TBL_CUSTOMER whether an ID matching the Customer_ID of that row. If so it would connect that row of TBL_CUSTOMER with the row of TBL_SALES. If it would find multiple rows with ID=1 in TBL_CUSTOMER it would combine each of these rows with the matching TBL_SALES row.
The syntax is pretty easy. I marked the relevant keywords bold. For joining two tables you just write the keyword JOIN between these tables. After the second table you have to write the key ON followed by the joining condition. The joining condition tells the database on which columns in the two tables the join is to be performed. You can also join over two or more columns. These conditions can be added by AND.
In the above example we want to join the two tables over the ID and Customer_ID column. So we just write …. ON c.ID=t.Customer_ID. After combining the two tables the results are aggregated.
Now imagine the Customer table looks like this (because of an error or whatever):
| ID | Name | Age |
| 1 | Gaussling | 32 |
| 2 | Smith | 45 |
| 3 | Meier | 25 |
| 2 | Gaussling2 | 33 |
Table1: TBL_CUSTOMER
Our SQL looks like this
SELECT *
FROM TBL_CUSTOMER c JOIN TBL_SALES t ON c.ID=t.Customer_ID
Now the result set would look like this:
| ID | Name | Age | Date | Customer_Id | Turnover |
| 1 | Gaussling | 32 | 2013-12-01 | 1 | 100€ |
| 1 | Gaussling | 32 | 2013-12-02 | 1 | 50€ |
| 2 | Smith | 45 | 2013-12-02 | 2 | 200€ |
| 3 | Meier | 25 | 2013-12-03 | 3 | 75€ |
| 2 | Gaussling2 | 33 | 2013-12-02 | 2 | 200€ |
The green marked row is the one out of the TBL_SALES that is duplicated now because in the TBL_CUSTOMER there are two rows with the ID=2.
I hope it got a little clearer what an inner join is and how it works. Also I tried to figure how some strange results (with duplicated) rows might occur. In future articles I will also explain the other join types.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!
