Using recursive queries

Edit online

Some applications work with data that is recursive in nature. To query this type of data, you can use a hierarchical query or a recursive common table expression.

One example of recursive data is a Bill of Materials (BOM) application that works with the expansion of parts and its component subparts. For example, a chair might be made of a seat unit and a leg assembly. The seat unit might consist of a seat and two arms. Each of these parts can be further broken down into its subparts until there is a list of all the parts needed to build a chair.

Db2® for i provides two ways of defining a recursive query. The first one is called a hierarchical query which uses the CONNECT BY clause to define how a parent row is to be associated with its child rows. The second method is to use a recursive common table expression. This uses a common table expression to define the first, or seed, rows and then uses a UNION to define how the child rows are determined.

Each of these methods of defining a recursive query has advantages and disadvantages. The CONNECT BY syntax is much simpler to understand, but has fewer ways to derive the data in its query. CONNECT BY can be specified in any subselect anywhere in a query. A recursive common table expression has more options for how the union is defined to generate the child rows.

There are a couple of behavioral differences between a connect by recursive query and a recursive common table expression query. First, they differ in how they handle cyclic data. This difference is discussed in the examples. Second, connect by allows a sort among siblings. This is also shown in the examples. Finally, the two implementations differ in how the data is put on a queue that is used to implement the recursion. By default a recursive common table expression's data tends to come out in breadth first order, first in first out. With connect by, the order is designed to come out depth first. This means that rows in a recursive step immediately follow their parent row. The recursive common table expression syntax gives you a choice of depth or breadth first hierarchical order by adding the SEARCH clause. The connect by syntax is always depth first.

In the trip planner examples for these recursive methods, airline flights and train connections are used to find transportation paths between cities. The following table definitions and data are used in the examples.

CREATE TABLE FLIGHTS (DEPARTURE CHAR(20),
                      ARRIVAL CHAR(20), 
                      CARRIER CHAR(15),
                      FLIGHT_NUMBER CHAR(5), 
                      PRICE INT);


INSERT INTO FLIGHTS VALUES('New York', 'Paris', 'Atlantic', '234', 400);
INSERT INTO FLIGHTS VALUES('Chicago', 'Miami', 'NA Air', '2334', 300);
INSERT INTO FLIGHTS VALUES('New York', 'London', 'Atlantic', '5473', 350);
INSERT INTO FLIGHTS VALUES('London', 'Athens'  , 'Mediterranean', '247', 340);
INSERT INTO FLIGHTS VALUES('Athens', 'Nicosia' , 'Mediterranean', '2356', 280); 
INSERT INTO FLIGHTS VALUES('Paris', 'Madrid' , 'Euro Air',  '3256', 380);
INSERT INTO FLIGHTS VALUES('Paris', 'Cairo' , 'Euro Air', '63', 480);
INSERT INTO FLIGHTS VALUES('Chicago', 'Frankfurt', 'Atlantic', '37', 480);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Moscow', 'Asia Air', '2337', 580);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Beijing', 'Asia Air',  '77', 480); 
INSERT INTO FLIGHTS VALUES('Moscow', 'Tokyo', 'Asia Air', '437', 680);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Vienna', 'Euro Air', '59', 200);
INSERT INTO FLIGHTS VALUES('Paris', 'Rome', 'Euro Air', '534', 340);
INSERT INTO FLIGHTS VALUES('Miami', 'Lima', 'SA Air', '5234', 530);
INSERT INTO FLIGHTS VALUES('New York', 'Los Angeles', 'NA Air', '84', 330);
INSERT INTO FLIGHTS VALUES('Los Angeles', 'Tokyo', 'Pacific Air', '824', 530);
INSERT INTO FLIGHTS VALUES('Tokyo', 'Hawaii', 'Asia Air', '94', 330);
INSERT INTO FLIGHTS VALUES('Washington', 'Toronto', 'NA Air', '104', 250);

CREATE TABLE TRAINS(DEPARTURE CHAR(20), 
                    ARRIVAL CHAR(20),
                    RAILLINE CHAR(15), 
                    TRAIN CHAR(5), 
                    PRICE INT); 

INSERT INTO TRAINS VALUES('Chicago', 'Washington', 'UsTrack', '323', 90;
INSERT INTO TRAINS VALUES('Madrid', 'Barcelona', 'EuroTrack', '5234', 60);
INSERT INTO TRAINS VALUES('Washington' , 'Boston' , 'UsTrack', '232', 50);

CREATE TABLE FLIGHTSTATS(FLIGHT_NO CHAR(5), 
                         ON_TIME_PERCENT DECIMAL(5,2), 
                         CANCEL_PERCENT DECIMAL(5,2)); 

INSERT INTO FLIGHTSTATS VALUES('234', 85.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('2334',  92.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('5473', 86.2, 0.10);
INSERT INTO FLIGHTSTATS VALUES('247',  91.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('2356', 91.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('3256', 92.0 , 0.10);
INSERT INTO FLIGHTSTATS VALUES('63', 90.5 , 0.10);
INSERT INTO FLIGHTSTATS VALUES('37', 87.0 , 0.20);
INSERT INTO FLIGHTSTATS VALUES('2337', 80.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('77', 86.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('437', 81.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('59',  85.0, 01.0);
INSERT INTO FLIGHTSTATS VALUES('534', 87.0 , 01.0);
INSERT INTO FLIGHTSTATS VALUES('5234', 88.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('84', 88.0, 0.1);
INSERT INTO FLIGHTSTATS VALUES('824', 93.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('94', 92.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('104', 93.0, 0.10);