Specify a test for matching rows.

 

Syntax

 

<match predicate> ::=

 

<match predicate part 2> ::=

 

 

 

MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ] <table subquery>

 

 

Notes

 

The degree of the row value predicand and table subquery shall be 1.



 

The data types shall be comparable.



 

If neither SIMPLE, PARTIAL, nor FULL is specified, then SIMPLE is implicit. Since this version of NexusDB only allows row value constructors with a degree of 1 (one column), specifying SIMPLE, PARTIAL or FULL will evaluate to exactly the same result.



 

The predicate evaluates to TRUE if the row value predicand is null, or the row value predicand is different from null and there is at least one row in the subquery result matching the row value predicand, else to FALSE.



 

If UNIQUE is specified, then the predicate evaluates to TRUE only if there is exactly one row in the subquery result matching the row value predicand.



 

 

Examples

 

1)

The following example selects students enrolled in any number of courses:



 


SELECT studentID, studentName


FROM students


WHERE studentID MATCH (




SELECT studentID


FROM enrolls




)



 

2)

The following example selects students enrolled in exactly 1 course:



 


SELECT studentID, studentName


FROM students


WHERE studentID MATCH UNIQUE (




SELECT studentID


FROM enrolls




)



 

 


 

Conformance

 

SQL:2003 standard

-

Feature F741 "Referential MATCH types"