Solutions

Solutions to the join exercises.

Exploring Join Variations: Solution

  1. The query doesn't publish any rows to TradeResults, because TradesWindow doesn't contain any rows with YHOO in the Symbol column.

  2. The query published three rows of output to TradeResults, because TradesWindow contained three rows with EBAY in the Symbol column. The SELECT clause of the query processed the combination of the incoming TradeInquiries row with each of these three TradesWindow rows.

  3. The output stream contains as many rows as are in TradesWindow when you send the row into Tradenquiries. Because the query has no ON clause join condition, Sybase CEP Engine paired the incoming TradeInquiries row with each row contained in TradesWindow, regardless of the values in the various columns.

    In real-world applications, joins without a join condition are rare, as such queries tend to have limited usefulness and generate a large number of output rows.

  4. The output is the same as for a query using "ON TradeInquiries.Symbol = TradesWindow.Symbol". It is customary to use an ON clause in a query that uses a JOIN keyword in its FROM clause, but for an inner join you can use a WHERE clause to set the condition instead, with the same result.

Creating an inner join: Solution

Creating an outer join: Solution

  1. This query contains two errors:

    • The FROM clause creates a left outer join with a window on the left side of the join and a data stream on the right side. A left outer join between a data stream and a window must list the stream on the left.

    • The ON clause of an outer join can only include equality comparisons, so "Tw.Price > 25" is not valid.

    The following syntax is valid:

    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 AND Tw.Price > 25;
    
  2. This right outer join query produced identical results to the left outer join query you used in the first activity. The SELECT clause of a right outer join processes any row that arrives in the query's right data source, whether or not there are any rows in the left data source that meet the join condition. If no rows in the left data source meet the join condition, items that refer to columns in the left data source publish NULLs in the output row. Because a stream-to-window join executes only when a row arrives in the stream, a right outer join between a stream and a window must list the stream on the right.