In a one-to-many join relationship, one row in one table potentially matches with one or more rows in another table, and there is not more than one row in the first table that matches with the same row(s) in the second table. For this to be true, the values in the join column in the first table must be unique.
It is possible that either table has no match on the other table. This constitutes an outer join. Sybase IQ fully supports outer joins. For more information, see the Performance and Tuning Guide.
If the join column is made up of more than one column, the combination of the values must be unique on the “one” side. For example, in the iqdemo database, the ID in the Customers table and the CustomerID in the SalesOrders table each contain a customer ID. The Customers table contains one row for each customer and, therefore, has a unique value in the ID column in each row. The SalesOrders table contains one row for each transaction a customer has made. Presumably, there are many transactions for each customer, so there are multiple rows in the SalesOrders table with the same value in the CustomerID column.
So, if you join Customers.ID to SalesOrders.CustomerID, the join relationship is one-to-many. As you can see in the following example, for every row in Customers, there are potentially many matching rows in SalesOrders. (Output for example has been limited to 15 rows.)
SELECT SalesOrders.ID, SalesOrders.CustomerID, Customers.GivenName from SalesOrders, Customers where SalesOrders.CustomerID = Customers.ID
ID CustomerID GivenName ---- ---------- --------- 2001 101 Michaels 2005 101 Michaels 2125 101 Michaels 2206 101 Michaels 2279 101 Michaels 2295 101 Michaels 2337 101 Michaels 2389 101 Michaels 2447 101 Michaels 2560 101 Michaels 2583 101 Michaels 2002 101 Beth 2142 101 Beth 2318 101 Beth 2338 101 Beth
WARNING! If the one-to-many relationship is incorrect, the join cannot be synchronized until you remove the extra rows from the “one” table. If you try to synchronize, you get a Duplicate Row error, and the transaction rolls back.
When you create a join index, you use ANSI FULL OUTER join syntax. Sybase IQ stores the index as a full outer join. Later, when you issue queries against the columns in a join index, you can specify inner, left outer, and right outer join relationships as well as full outer joins. Sybase IQ uses only the parts of the join index needed for a given query.
