lojreorder.sql

derby database source code.good for you.

       (select a.price
        from k55admin.parts b left join k55admin.products a
        on 1=0)
order by 1,2,3,4;

-- 305 - '='/like/in in RIGHT JOIN ON condition with correlated '>=ALL' subquery; Error.
select part, p.num, product, pt.NUM, price
from k55admin.parts p right join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
and pt.price >=ALL
       (select a.price
        from k55admin.parts b left join k55admin.products a
        on 1=0
        where a.num>=pt.num)
order by 1,2,3,4;

-- 306 - '='/like/in in LEFT JOIN ON condition with uncorrelated scalar subquery; Error.
select part, p.num, product, pt.NUM, price
from k55admin.parts p left join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
and pt.price =
       (select max(a.price)
        from k55admin.parts b left join k55admin.products a
        on 1=0)
order by 1,2,3,4;

-- coj202.clp

---------------------------------------------------------------------
--      inner join (105)
---------------------------------------------------------------------

-- ---------------------------------------------------------------------;
-- test unit 1. plain joins, different relops, conjunction;
-- ---------------------------------------------------------------------;

-- 101 - multiple '>=', '<=' in INNER JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM >= K55ADMIN.PRODUCTS.NUM and K55ADMIN.PARTS.NUM <= K55ADMIN.PRODUCTS.NUM
    order by 1,2,3,4;

-- 102 - 'between' in INNER JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM between K55ADMIN.PRODUCTS.NUM and K55ADMIN.PRODUCTS.NUM + 50
    order by 1,2,3,4;

-- 103 - '>'/'<' in INNER JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM > K55ADMIN.PRODUCTS.NUM and K55ADMIN.PRODUCTS.NUM < K55ADMIN.PARTS.NUM
    order by 1,2,3,4;

-- 104 - '<>' in INNER JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM <> K55ADMIN.PRODUCTS.NUM
    order by 1,2,3,4;

-- 105 - multiple '>=', '<=' in LEFT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM >= K55ADMIN.PRODUCTS.NUM and K55ADMIN.PARTS.NUM <= K55ADMIN.PRODUCTS.NUM
    order by 1,2,3,4;

-- 106 - 'between' in LEFT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM between K55ADMIN.PRODUCTS.NUM and K55ADMIN.PRODUCTS.NUM + 50
    order by 1,2,3,4;

-- 107 - '>'/'<' in LEFT JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM > K55ADMIN.PRODUCTS.NUM and K55ADMIN.PRODUCTS.NUM < K55ADMIN.PARTS.NUM
    order by 1,2,3,4;

-- 108 - '<>' in LEFT JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM <> K55ADMIN.PRODUCTS.NUM
    order by 1,2,3,4;

-- 109 - multiple '>=', '<=' in RIGHT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM >= K55ADMIN.PRODUCTS.NUM and K55ADMIN.PARTS.NUM <= K55ADMIN.PRODUCTS.NUM
    order by 1,2,3,4;

-- 110 - 'between' in RIGHT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM between K55ADMIN.PRODUCTS.NUM and K55ADMIN.PRODUCTS.NUM + 50
    order by 1,2,3,4;

-- 111 - '>'/'<' in RIGHT JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM > K55ADMIN.PRODUCTS.NUM and K55ADMIN.PRODUCTS.NUM < K55ADMIN.PARTS.NUM
    order by 1,2,3,4;

-- 112 - '<>' in RIGHT JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM <> K55ADMIN.PRODUCTS.NUM
    order by 1,2,3,4;


-- ---------------------------------------------------------------------;
-- test unit 2. plain joins, different relops, and/or/not;
-- ---------------------------------------------------------------------;

-- 201 - '>='/'<='/OR in INNER JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM >= K55ADMIN.PRODUCTS.NUM OR K55ADMIN.PARTS.NUM <= K55ADMIN.PRODUCTS.NUM
    order by 1,2,3,4;

-- 202 - 'not between' in INNER JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS JOIN K55ADMIN.PRODUCTS
    ON NOT(K55ADMIN.PARTS.NUM between K55ADMIN.PRODUCTS.NUM AND K55ADMIN.PRODUCTS.NUM + 50)
    order by 1,2,3,4;

-- 203 - '>'/'<'/OR in INNER JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM > K55ADMIN.PRODUCTS.NUM OR K55ADMIN.PRODUCTS.NUM < K55ADMIN.PARTS.NUM
    order by 1,2,3,4;

-- 204 - not/'<>' in INNER JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS JOIN K55ADMIN.PRODUCTS
    ON NOT( K55ADMIN.PARTS.NUM <> K55ADMIN.PRODUCTS.NUM )
    order by 1,2,3,4;

-- 205 - '>='/'<='/or in LEFT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM >= K55ADMIN.PRODUCTS.NUM OR K55ADMIN.PRODUCTS.NUM <= K55ADMIN.PARTS.NUM
    order by 1,2,3,4;

-- 206 - 'not between' in LEFT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON not (K55ADMIN.PARTS.NUM between K55ADMIN.PRODUCTS.NUM and K55ADMIN.PRODUCTS.NUM + 50)
    order by 1,2,3,4;

-- 207 - '>'/'<'/or in LEFT JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM > K55ADMIN.PRODUCTS.NUM OR K55ADMIN.PRODUCTS.NUM < K55ADMIN.PARTS.NUM
    order by 1,2,3,4;

-- 208 - not/'<>' in LEFT JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON NOT(K55ADMIN.PARTS.NUM <> K55ADMIN.PRODUCTS.NUM)
    order by 1,2,3,4;

-- 209 - '>='/'<='/OR in RIGHT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM >= K55ADMIN.PRODUCTS.NUM or K55ADMIN.PRODUCTS.NUM <= K55ADMIN.PARTS.NUM
    order by 1,2,3,4;

-- 210 - 'not between' in RIGHT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON not(K55ADMIN.PARTS.NUM between K55ADMIN.PRODUCTS.NUM and K55ADMIN.PRODUCTS.NUM + 50)
    order by 1,2,3,4;

-- 211 - '>'/'<'/or in RIGHT JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM > K55ADMIN.PRODUCTS.NUM or K55ADMIN.PRODUCTS.NUM < K55ADMIN.PARTS.NUM
    order by 1,2,3,4;

-- 212 - not/'<>' in RIGHT JOIN on condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON not(K55ADMIN.PARTS.NUM <> K55ADMIN.PRODUCTS.NUM)
    order by 1,2,3,4;

-- ---------------------------------------------------------------------;
-- test unit 3. plain joins, like/in/is-null/is-not-null;
-- ---------------------------------------------------------------------;

-- 301 - '<>'/'=' in LEFT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON ( K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM or
         K55ADMIN.PARTS.NUM <> K55ADMIN.PRODUCTS.NUM)
    order by 1,2,3,4;

-- 302 - '='/like in LEFT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON ( K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM 
             or K55ADMIN.PRODUCTS.PRODUCT LIKE 'Screw%')
    order by 1,2,3,4;

-- 303 - '='/in in LEFT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON ( K55ADMIN.PRODUCTS.PRODUCT in ('Screwdriver','Saw') 
             or K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM )
    order by 1,2,3,4;


-- 304 - '='/is-null in LEFT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON ( K55ADMIN.PRODUCTS.PRODUCT is null 
             or K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM )
    order by 1,2,3,4;

-- 305 - '='/is-not-null in LEFT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON ( K55ADMIN.PRODUCTS.PRODUCT is not null 
             or K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM )
    order by 1,2,3,4;

-- 306 - '='/'<' in LEFT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON ( K55ADMIN.PARTS.NUM < K55ADMIN.PRODUCTS.NUM 
             or K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM )
    order by 1,2,3,4;

-- 307 - '='/like in LEFT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON ( K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM 
             or K55ADMIN.PRODUCTS.PRODUCT LIKE 'Nut%' )
    order by 1,2,3,4;

-- 308 - '='/like/in in RIGHT JOIN ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON ( K55ADMIN.PRODUCTS.PRODUCT in ('Bolt','Nuts') 
             or K55ADMIN.PRODUCTS.PRODUCT LIKE 'Power%' 
             or K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM )
    order by 1,2,3,4;

-- ---------------------------------------------------------------------;
-- test unit 4. plain joins, only local predicates, no join predicate;
-- ---------------------------------------------------------------------;

-- 401 - local '=' pred on tuple-preserving operand;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = 10
    order by 1,2,3,4;

-- 402 - local IN pred on tuple-preserving operand;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM IN (160, 205)
    order by 1,2,3,4;

-- 403 - local between pred on tuple-preserving operand;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM between 150 and 250
    order by 1,2,3,4;