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 Sybase IQ 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 asiqdemo database, the id in the customer table and the cust_id in the sales_order table each contain a customer ID. The customer table contains one row for each customer and, therefore, has a unique value in the id column in each row. The sales_order 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 sales_order table with the same value in the cust_id column.
So, if you join customer.id to sales_order.cust_id, the join relationship is one-to-many. As you can see in the following example, for every row in customer, there are potentially many matching rows in sales_order.
select sales_order.id, sales_order.cust_id, customer.lname from sales_order, customer where sales_order.cust_id = customer
id cust_id id lname 2583,101,101,'Devlin' 2001,101,101,'Devlin' 2005,101,101,'Devlin' 2125,101,101,'Devlin' 2206,101,101,'Devlin' 2279,101,101,'Devlin' 2295,101,101,'Devlin' 2002,102,102,'Reiser' 2142,102,102,'Reiser' 2318,102,102,'Reiser' 2338,102,102,'Reiser' 2449,102,102,'Reiser' 2562,102,102,'Reiser' 2585,102,102,'Reiser' 2340,103,103,'Niedringhaus' 2451,103,103,'Niedringhaus' 2564,103,103,'Niedringhaus' 2587,103,103,'Niedringhaus' 2003,103,103,'Niedringhaus' 2178,103,103,'Niedringhaus' 2207,103,103,'Niedringhaus' 2307,103,103,'Niedringhaus'
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.
