Creating an outer join: Exercise

So far, you have worked with inner joins - joins that produce output only when rows meet the join condition. For some applications you may want to produce output even if the join condition isn't met. For example, what if you want to know when an inquiry about a stock arrives even if it doesn't match any of the available trade information?

Follow these steps to produce output even when the join condition isn't met:

1. Replace the Query statement in the Queries tab with the following text (or copy the text from CombiningSourcesCCL.txt between the lines "-- Combining Sources Code Block 4" and "-- Combining Sources Code Block 5"):

   -- Create the join
   
   INSERT INTO TradesResults
   SELECT Ti.Symbol, 
      Tw.Price,
      GETTIMESTAMP(Tw)
   FROM TradeInquiries AS Ti LEFT OUTER JOIN TradesWindow AS Tw
   ON Ti.Symbol = Tw.Symbol;
   

   The new FROM clause changes the inner join to a left outer join, which publishes a row for every row in the left data source (TradeInquiries) even if the join condition isn't met.

   Notice that the FROM clause contains AS clauses, which define an alias for each of the data sources. This example defines an alias for every data source, but you can define aliases for just a subset, if you want. Every reference to the data sources in the rest of the statement now use the alias. Once you define an alias for a data source, you must refer to that data source with the alias throughout the statement.

2. Start the project and then examine the viewer for TradeInquiries:

   
   
   

3. Compare those rows to the output in TradeResults:

   
   
   

   Notice that some of the output rows contain Nulls. When a row arrives in TradeInquiries for which there are no rows in TradesWindow that meet the join condition, the columns in the output row that would otherwise contain data from TradesWindow contain Nulls.

4. Examine the Query statement syntax again. The FROM clause of a left outer join between a stream and a window must list the stream on the left side of the join.

5. Stop the project.

6. Replace the Query statement in the Queries tab with the following text (or copy the text from CombiningSourcesCCL.txt between the line "-- Combining Sources Code Block 5" and the end of the file):

   -- Create the join
   
   INSERT INTO TradeResults
   SELECT Ti.Symbol, 
      Tw.Price,
      GETTIMESTAMP(Tw)
   FROM TradeInquiries AS Ti LEFT OUTER JOIN TradesWindow AS Tw
   ON Ti.Symbol = Tw.Symbol
   WHERE Tw.Volume > 1000;
   

   This variation simply adds a WHERE clause to filter the output.

7. Start the project and then compare the output to the previous results:

   
   
   

   Notice that the output no longer contains Nulls:

   • In a left outer join, the join condition in the ON clause is processed first. A left outer join requires an ON clause and uses a more restrictive form of the ON clause than an inner join: it must consist only of one or more equality comparisons, each of which compares a column in the left data source with a column in the right data source. If you use more than one equality comparison, you must separate them with the keyword AND.

   • If your query includes a WHERE clause, as in this case, Sybase CEP Engine applies the filter to the results of the join. Because the filter in this WHERE clause is based on a column in TradesWindow, Sybase CEP Engine will only produce output rows when the join condition is met. Any rows from the join that don't include values from TradesWindow cannot pass the WHERE filter.

8. Delete the query's WHERE clause and then stop the project.

Exercises

Complete the following exercises to explore different types of joins. See Solutions for possible solutions to these exercises.