MATCH (Transact-SQL)

**APPLIES TO:** SQL Server (starting with 2017) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

Specifies a search condition for a graph. MATCH can be used only with graph node and edge tables, in the SELECT statement as part of WHERE clause.

Transact-SQL Syntax Conventions

Syntax

MATCH (<graph_search_pattern>)

<graph_search_pattern>::=
    {<node_alias> { 
                     { <-( <edge_alias> )- } 
                   | { -( <edge_alias> )-> }
                 <node_alias> 
                 } 
     }
     [ { AND } { ( <graph_search_pattern> ) } ]
     [ ,...n ]
  
<node_alias> ::=
    node_table_name | node_alias 

<edge_alias> ::=
    edge_table_name | edge_alias

Arguments

graph_search_pattern
Specifies the pattern to search or path to traverse in the graph. This pattern uses ASCII art syntax to traverse a path in the graph. The pattern goes from one node to another via an edge, in the direction of the arrow provided. Edge names or aliases are provided inside parantheses. Node names or aliases appear at the two ends of the arrow. The arrow can go in either direction in the pattern.

node_alias
Name or alias of a node table provided in the FROM clause.

edge_alias
Name or alias of an edge table provided in the FROM clause.

Remarks

The node names inside MATCH can be repeated. In other words, a node can be traversed an arbitrary number of times in the same query.
An edge name cannot be repeated inside MATCH.
An edge can point in either direction, but it must have an explicit direction.
OR and NOT operators are not supported in the MATCH pattern. MATCH can be combined with other expressions using AND in the WHERE clause. However, combining it with other expressions using OR or NOT is not supported.

Examples

A. Find a friend

The following example creates a Person node table and friends Edge table, inserts some data and then uses MATCH to find friends of Alice, a person in the graph.

-- Create person node table
CREATE TABLE dbo.Person (ID integer PRIMARY KEY, name varchar(50)) AS NODE;
CREATE TABLE dbo.friend (start_date DATE) AS EDGE;

-- Insert into node table
INSERT INTO dbo.Person VALUES (1, 'Alice');
INSERT INTO dbo.Person VALUES (2,'John');
INSERT INTO dbo.Person VALUES (3, 'Jacob');

-- Insert into edge table
INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'Alice'),
       (SELECT $node_id FROM dbo.Person WHERE name = 'John'), '9/15/2011');

INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'Alice'),
       (SELECT $node_id FROM dbo.Person WHERE name = 'Jacob'), '10/15/2011');

INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'John'),
       (SELECT $node_id FROM dbo.Person WHERE name = 'Jacob'), '10/15/2012');

-- use MATCH in SELECT to find friends of Alice
SELECT Person2.name AS FriendName
FROM Person Person1, friend, Person Person2
WHERE MATCH(Person1-(friend)->Person2)
AND Person1.name = 'Alice';

### B. Find friend of a friend The following example tries to find friend of a friend of Alice.

SELECT Person3.name AS FriendName 
FROM Person Person1, friend, Person Person2, friend friend2, Person Person3
WHERE MATCH(Person1-(friend)->Person2-(friend2)->Person3)
AND Person1.name = 'Alice';

C. More MATCH patterns

Following are some more ways in which a pattern can be specified inside MATCH.

-- Find a friend
   SELECT Person2.name AS FriendName
   FROM Person Person1, friend, Person Person2
   WHERE MATCH(Person1-(friend)->Person2);
   
-- The pattern can also be expressed as below

   SELECT Person2.name AS FriendName
   FROM Person Person1, friend, Person Person2 
   WHERE MATCH(Person2<-(friend)-Person1);


-- Find 2 people who are both friends with same person
   SELECT Person1.name AS Friend1, Person2.name AS Friend2
   FROM Person Person1, friend friend1, Person Person2, 
        friend friend2, Person Person0
   WHERE MATCH(Person1-(friend1)->Person0<-(friend2)-Person2);
   
-- this pattern can also be expressed as below

   SELECT Person1.name AS Friend1, Person2.name AS Friend2
   FROM Person Person1, friend friend1, Person Person2, 
        friend friend2, Person Person0
   WHERE MATCH(Person1-(friend1)->Person0 AND Person2-(friend2)->Person0);

See Also

CREATE TABLE (SQL Graph)
INSERT (SQL Graph)]
Graph processing with SQL Server 2017