UNION ALL Scope Specifications
Microsoft SharePoint Portal Server Search (SharePointPSSearch) supports searching in multiple content indexes through the UNION ALL scope specification. The syntax follows:
(<union_all_specifier>) [AS] <scope_alias>
The UNION ALL specifier construct specifies the content indexes where the search is performed. The AS keyword and scope alias are optional. If you include the scope alias, it must be a valid regular or delimited identifier.
Following is the UNION ALL specifier syntax:
TABLE Portal_Content..Scope() UNION ALL TABLE Non_Portal_Content..Scope()
[UNION ALL TABLE <qualified_scope>] [AS] <scope_alias>…
Each TABLE keyword specifies that the qualified scope is to be treated as a separate table. UNION ALL specifies that the two tables be combined in the UNION manner.
Note UNION ALL is not the same as a database JOIN; a UNION ALL effectively combines the two tables into a single table having all the rows and columns.
The search query can combine more than two search scopes by simply adding UNION ALL keywords and TABLE specifiers.
Example
SELECT "DAV:href","DAV:displayname"
FROM (TABLE Portal_Content..Scope()
UNION ALL TABLE Non_Portal_Content..Scope()) WHERE…
SELECT "DAV:href","DAV:displayname"
FROM (TABLE Portal_Content..Scope()
UNION ALL TABLE Non_Portal_Content..Scope()) AS MyNewScope WHERE…