Tag Archiv für Join condition

Outer joins explained

This time I would like to explain outer joins in Oracle. In a different article I already described what inner joins are and how to use them in Oracle SQL. This time we will step forward and take a closer look at the second important join type: LEFT and RIGHT OUTER JOINS.

When we talked about inner joins I explained that two tables are connected by one or more columns called the join condition. When these columns of both tables match the rows are connected (i.e. joined) and returned in the result set. When they don’t match then they are not returned.  That’s where the outer join comes into play. This join type returns all rows of one of the two tables and connects the rows of the other table where it is possible. If not then these columns in the result are NULL. Depending of which of the two tables all rows are returned it is called a LEFT or RIGHT OUTER JOIN. There is also a FULL OUTER JOIN. But that is not described in this article.

Let’s arrange a little example for that:

DATE CUSTOMER_ID TURNOVER
2013-12-12 1 1000
2013-12-12 2 1500
2013-12-12 4 500

Fig1.: TBL_SALES

ID NAME CITY
1 Smith London
2 Maier Berlin
3 Gaussling Hamburg

Fig2.: TBL_CUSTOMER

Our SQL looks like this:

SELECT *
FROM TBL_CUSTOMER c LEFT JOIN TBL_SALES s ON c.ID=s.CUSTOMER_ID

The result-set is this:

ID NAME CITY DATE CUSTOMER_ID TURNOVER
1 Smith London 2013-12-12 1 1000
2 Maier Berlin 2013-12-12 2 1500
3 Gaussling Hamburg 2013-12-12 NULL NULL

As you can see Oracle takes all rows of TBL_CUSTOMER and for each row it tries to find a match in TBL_SALES. Where it is found it is taken, where not the result is NULL. Exchange LEFT with RIGHT in the SQL script and the result looks this way:

DATE CUSTOMER_ID TURNOVER ID NAME CITY
2013-12-12 1 1000 1 Smith London
2013-12-12 2 1500 2 Maier Berlin
2013-12-12 4 500 NULL NULL NULL

Now it is the other way round. All rows of TBL_SALES are returned and only the matches in TBL_CUSTOMER.

With the next example you can easily find the rows that have no matches in TBL_CUSTOMER, maybe for quality reasons or whatever:

SELECT s.*,
       c.ID
FROM TBL_CUSTOMER c RIGHT JOIN TBL_SALES s ON c.ID=s.CUSTOMER_ID
WHERE c.ID IS NULL

With that you just receive the last row of TBL_SALES.

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

Inner joins explained

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