semijoin

The semijoin is a variant of NESTED LOOP JOIN operator, and includes the NESTED LOOP JOIN operator in its result set. When you make a semi-join between two tables, Adaptive Server returns the rows from the first table that contain one or more matches in the second table (a regular join returns the matching rows from the first table only once). That is, instead of scanning a table to return all matching values, an semijoin returns rows when it finds the first matching value and then stops processing. Semijoins are also known as “existence joins.”

For example, if you perform a semijoin on the titles and titleauthor tables:

select title
from titles
where title_id in (select title_id from titleauthor)
and title like "A Tutorial%"

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
    The type of query is SELECT.

    4 operator(s) under root

|ROOT:EMIT Operator (VA = 4)
   |
   |   |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Left Semi Join)
   |   |
   |   |   |RESTRICT Operator (VA = 1)(0)(0)(0)(6)(0)
   |   |   |
   |   |   |   |SCAN Operator (VA = 0)
   |   |   |   | FROM TABLE
   |   |   |   | titles
   |   |   |   | Index : titleind
   |   |   |   | Forward Scan.
   |   |   |   | Positioning by key.
   |   |   |   | Keys are:
   |   |   |   | title ASC
   |   |   |   | Using I/O Size 2 Kbytes for index leaf pages.
   |   |   |   | With LRU Buffer Replacement Strategy for index leaf pages.
   |   |   |   | Using I/O Size 2 Kbytes for data pages.
   |   |   |   | With LRU Buffer Replacement Strategy for data pages.
   |   |
   |   |   |SCAN Operator (VA = 2)
   |   |   | FROM TABLE
   |   |   | titleauthor
   |   |   | Index : titleidind
   |   |   | Forward Scan.
   |   |   | Positioning by key.
   |   |   | Index contains all needed columns. Base table will not be read.
   |   |   | Keys are:
   |   |   | title_id ASC
   |   |   | Using I/O Size 2 Kbytes for index leaf pages.
   |   |   | With LRU Buffer Replacement Strategy for index leaf pages.