Solutions to the join exercises.
The query doesn't publish any rows to TradeResults, because TradesWindow doesn't contain any rows with YHOO in the Symbol column.
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.
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.
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.
Here is the query, rewritten to use comma-separated join syntax:
INSERT INTO FrequentWinners SELECT PlayerScore.ID, PlayerScore.WinningNumber FROM PlayerScore, DailyRecord WHERE PlayerScore.Win = TRUE AND PlayerScore.ID = DailyRecord.ID AND DailyRecord.DailyWins > 3;
Notice that the SELECT clause of this query refers to only one of the query's data sources.
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;
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.