Category: ASE: Query Processing and Abstract Plan

Join Operators, Joins

If data is retrieved from two or more tables or views ASE uses joins. In the process, the tables or views are compared by defining a column in each object and trying to match the values in each, row by row. Rows with matching values will be linked. It is important that the compared values are of the same or a similar datatype or that they can be adapted using the convert() function. (The system procedure sp_helpjoins can be used to determine which columns of a set of tables might qualify for a join.) The result set is then stored in a new table. The objects specified in a join can reside in the same or in different databases. Joins can often be written as subqueries. Joins can be embedded in select, update, insert, delete statements or subqueries. Further restrictions and conditions can be added to the join condition. The "from" clause of the query specifies the tables which are part of the join. The number of tables for a join must not exceed 16. The "where" clause determines which rows are part of the resultset of the join.

Joins: Operators

Tables in joins can be combined with the usual T-SQL logical operators.

Joins: Types

There are several types of joins, like for example equijoins, natural joins and outer joins. The most frequently used type is the equijoin, based on equality of values:

    select au_fname, au_lname, pub_name 
    from authors, publishers 
    where authors.city = publishers.city
    

Here, the result of the query is generated from the data of two different tables; the tables are joined, using the column "city" to link both datasets.

Results of joins containing all rows, independent on whether matching rows exist, are called outer joins. ASE distinguishes left and right outer joins. The following example joins the "titles" and "titleauthor" tables on the column "title_id":

    select * 
    from titles, titleauthor
    where titles.title_id *= titleauthor.title_id
    

In this example, the left outer join is indicated by the "*="-sign in T-SQL notation. The ANSI standard used for this purpose the key words left join and right join. The terms inner table and outer table describe the placement of tables in an outer join. The "outer table" , or respectively "inner table" in a 'left join' are the corresponding left and right table in the query. In a 'right join', the outer table is the right hand side table of the query and the inner table is the left hand side table of the query:

    A left join B
    B right join A
    A *= B
    B =* A
    

In the above example, table A is the outer and table B is the inner table of the join.

Joins: Strategies

ASE basically provides four join strategies:

• NestedLoopJoin
• MergeJoin
• HashJoin
• NaryNestedJoin, as a special variant of the NestedLoopJoin