github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/join/join.test (about)

     1  drop table if exists t1;
     2  drop table if exists t2;
     3  CREATE TABLE t1 (S1 INT);
     4  CREATE TABLE t2 (S1 INT);
     5  INSERT INTO t1 VALUES (1),(3),(4),(6);
     6  INSERT INTO t2 VALUES (2),(4),(5);
     7  SELECT * FROM t1 JOIN t2 on t1.S1=t2.S1;
     8  drop table if exists t1;
     9  drop table if exists t2;
    10  create table t1 (id int);
    11  create table t2 (id int);
    12  insert into t1 values (75);
    13  insert into t1 values (79);
    14  insert into t1 values (78);
    15  insert into t1 values (77);
    16  insert into t1 values (76);
    17  insert into t1 values (76);
    18  insert into t1 values (104);
    19  insert into t1 values (103);
    20  insert into t1 values (102);
    21  insert into t1 values (101);
    22  insert into t1 values (105);
    23  insert into t1 values (106);
    24  insert into t1 values (107);
    25  insert into t2 values (107),(75),(1000);
    26  select t1.id, t2.id from t1, t2 where t2.id = t1.id;
    27  select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id;
    28  select t1.id,t2.id from t2 join t1 on t1.id=t2.id where t2.id=75;
    29  drop table if exists t1;
    30  drop table if exists t2;
    31  CREATE TABLE t1 (
    32  id int,
    33  token varchar(100) DEFAULT '' NOT NULL,
    34  count int DEFAULT 0 NOT NULL,
    35  qty int,
    36  phone char(1) DEFAULT '' NOT NULL,
    37  times datetime DEFAULT '2000-01-01 00:00:00' NOT NULL
    38  );
    39  INSERT INTO t1 VALUES (21,'e45703b64de71482360de8fec94c3ade',3,7800,'n','1999-12-23 17:22:21');
    40  INSERT INTO t1 VALUES (22,'e45703b64de71482360de8fec94c3ade',4,5000,'y','1999-12-23 17:22:21');
    41  INSERT INTO t1 VALUES (18,'346d1cb63c89285b2351f0ca4de40eda',3,13200,'b','1999-12-23 11:58:04');
    42  INSERT INTO t1 VALUES (17,'ca6ddeb689e1b48a04146b1b5b6f936a',4,15000,'b','1999-12-23 11:36:53');
    43  INSERT INTO t1 VALUES (16,'ca6ddeb689e1b48a04146b1b5b6f936a',3,13200,'b','1999-12-23 11:36:53');
    44  INSERT INTO t1 VALUES (26,'a71250b7ed780f6ef3185bfffe027983',5,1500,'b','1999-12-27 09:44:24');
    45  INSERT INTO t1 VALUES (24,'4d75906f3c37ecff478a1eb56637aa09',3,5400,'y','1999-12-23 17:29:12');
    46  INSERT INTO t1 VALUES (25,'4d75906f3c37ecff478a1eb56637aa09',4,6500,'y','1999-12-23 17:29:12');
    47  INSERT INTO t1 VALUES (27,'a71250b7ed780f6ef3185bfffe027983',3,6200,'b','1999-12-27 09:44:24');
    48  INSERT INTO t1 VALUES (28,'a71250b7ed780f6ef3185bfffe027983',3,5400,'y','1999-12-27 09:44:36');
    49  INSERT INTO t1 VALUES (29,'a71250b7ed780f6ef3185bfffe027983',4,17700,'b','1999-12-27 09:45:05');
    50  CREATE TABLE t2 (
    51  id int,
    52  category int DEFAULT 0 NOT NULL,
    53  county int DEFAULT 0 NOT NULL,
    54  state int DEFAULT 0 NOT NULL,
    55  phones int DEFAULT 0 NOT NULL,
    56  nophones int DEFAULT 0 NOT NULL
    57  );
    58  INSERT INTO t2 VALUES (3,2,11,12,5400,7800);
    59  INSERT INTO t2 VALUES (4,2,25,12,6500,11200);
    60  INSERT INTO t2 VALUES (5,1,37,6,10000,12000);
    61  select t1.id, category as catid, state as stateid, county as countyid from t1 join t2 on count=t2.id where token='a71250b7ed780f6ef3185bfffe027983';
    62  select t1.id, category as catid, state as stateid, county as countyid from t1 join t2 on count=t2.id where token='a71250b7ed780f6ef3185bfffe027983' and t1.id>26 order by t1.id;
    63  drop table if exists t1;
    64  drop table if exists t2;
    65  drop table if exists t3;
    66  CREATE TABLE t1 (
    67  t1_id int default NULL,
    68  t2_id int default NULL,
    69  type varchar(12) default NULL,
    70  cost_unit varchar(5) default NULL,
    71  min_value double default NULL,
    72  max_value double default NULL,
    73  t3_id int default NULL,
    74  item_id int default NULL
    75  );
    76  CREATE TABLE t2 (
    77  id int  NOT NULL,
    78  name varchar(255) default NULL,
    79  PRIMARY KEY  (id)
    80  );
    81  INSERT INTO t1 VALUES (12,5,'Percent','Cost',-1,0,-1,-1),(14,4,'Percent','Cost',-1,0,-1,-1),(18,5,'Percent','Cost',-1,0,-1,-1),(19,4,'Percent','Cost',-1,0,-1,-1),(20,5,'Percent','Cost',100,-1,22,291),(21,5,'Percent','Cost',100,-1,18,291),(22,1,'Percent','Cost',100,-1,6,291),(23,1,'Percent','Cost',100,-1,21,291),(24,1,'Percent','Cost',100,-1,9,291),(25,1,'Percent','Cost',100,-1,4,291),(26,1,'Percent','Cost',100,-1,20,291),(27,4,'Percent','Cost',100,-1,7,202),(28,1,'Percent','Cost',50,-1,-1,137),(29,2,'Percent','Cost',100,-1,4,354),(30,2,'Percent','Cost',100,-1,9,137),(93,2,'Cost','Cost',-1,10000000,-1,-1);
    82  INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5');
    83  select t2_id,name, type  from t1 join t2 on t2.id=t1.t2_id order by id;
    84  drop table t1;
    85  drop table t2;
    86  CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255));
    87  CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255));
    88  INSERT INTO t1 VALUES (1, 'A');
    89  INSERT INTO t2 VALUES (1, 'B');
    90  SELECT t1.ID,Value2 FROM t1 JOIN t2 on t1.ID=t2.ID WHERE Value1 = 'A';
    91  SELECT t1.ID,Value2 FROM t1 JOIN t2 on t1.ID=t2.ID WHERE Value1 = 'A' and Value2 <> 'B';
    92  drop table if exists t1;
    93  drop table if exists t2;
    94  drop table if exists t3;
    95  CREATE TABLE t1 (a int);
    96  CREATE TABLE t2 (b int);
    97  CREATE TABLE t3 (c int);
    98  insert into t1 values(1),(2),(3),(4),(5);
    99  insert into t2 values(1),(3),(5),(7),(9);
   100  insert into t3 values(1),(1),(3),(4),(7);
   101  select a,b,c from t1 join t2 on t1.a=t2.b join t3 on t1.a=t3.c where a>1;
   102  drop table if exists t1;
   103  drop table if exists t2;
   104  drop table if exists t3;
   105  create table t1 (i int);
   106  create table t2 (i int);
   107  create table t3 (i int);
   108  insert into t1 values(1),(2);
   109  insert into t2 values(2),(3);
   110  insert into t3 values (2),(4);
   111  select t3.i from t1 join t2 on t1.i=t2.i join t3 on t2.i=t3.i;
   112  drop table if exists t1;
   113  drop table if exists t2;
   114  drop table if exists t3;
   115  drop table if exists t4;
   116  drop table if exists t5;
   117  drop table if exists t6;
   118  create table t1 (c int, b int);
   119  create table t2 (a int, b int);
   120  create table t3 (b int, c int);
   121  create table t4 (y int, c int);
   122  create table t5 (y int, z int);
   123  create table t6 (a int, c int);
   124  insert into t1 values (10,1);
   125  insert into t1 values (3 ,1);
   126  insert into t1 values (3 ,2);
   127  insert into t2 values (2, 1);
   128  insert into t3 values (1, 3);
   129  insert into t3 values (1,10);
   130  insert into t4 values (11,3);
   131  insert into t4 values (2, 3);
   132  insert into t5 values (11,4);
   133  insert into t6 values (2, 3);
   134  select distinct a,t1.b,t3.c from t1 join t2 on t1.b=t2.b join t3 on t1.b=t3.c;
   135  drop table if exists t1;
   136  drop table if exists t2;
   137  drop table if exists t3;
   138  drop table if exists t4;
   139  create table t1 (a1 int, a2 int);
   140  create table t2 (a1 int, b int);
   141  create table t3 (c1 int, c2 int);
   142  create table t4 (c2 int);
   143  insert into t1 values (1,1);
   144  insert into t2 values (1,1);
   145  insert into t3 values (1,1);
   146  insert into t4 values (1);
   147  select * from t1 join t2 on t1.a1=t2.a1 join t3 on b=c1 join t4 on t3.c2=t4.c2;
   148  drop table if exists t1;
   149  create table t1 (a int);
   150  insert into t1 values(1);
   151  drop table if exists t2;
   152  create table t2 (a int);
   153  insert into t2 values(1);
   154  select * from (t1 join t2 on t1.a = t2.a);
   155  
   156  drop table if exists tt;
   157  drop table if exists tt2;
   158  create table tt(a text,b text,c int);
   159  create table tt2(a text,b text,c int);
   160  insert into tt values("a","bc",1);
   161  insert into tt2 values("ab","c",2);
   162  select * from tt as t11 join tt2 as t12 on t11.a = t12.a and t11.b = t12.b;
   163  drop table if exists t1;
   164  drop table if exists t2;
   165  drop table if exists t3;
   166  create table t1 (q int);
   167  create table t2 (a int);
   168  create table t3 (b int);
   169  select * from t1,t2 join t3 on a=b;
   170  
   171  drop table if exists t1;
   172  drop table if exists t2;
   173  create table t1(a int, b int primary key);
   174  create table t2(a int, b int,primary key(a,b));
   175  insert into t1 select result%50,result from generate_series(1,10000,1) g;
   176  insert into t2 select result/100,result%100 from generate_series(1,10000,1) g;
   177  select t1.a,t1.b from t1 join t2 on t1.b=t2.a and t1.b=t2.b order by t1.a desc limit 4;
   178  select t2.a,t2.b from t2 join t1 on t2.a=t1.b and t2.b=t1.a order by t2.a desc limit 4;
   179  drop table t1;
   180  drop table t2;
   181  create table t1(a int, b int primary key);
   182  create table t2(a int, b int,primary key(a,b));
   183  insert into t1 select 10000-result,result from generate_series(1,10000,1) g;
   184  insert into t2 select result%2,result-result%2 from generate_series(1,10000,1) g;
   185  select t1.a,t1.b,t2.a,t2.b from t1 left join t2 on t1.b=t2.b order by t1.b desc limit 4;
   186  select t1.a,t1.b,t2.a,t2.b from t1 right join t2 on t1.b=t2.b order by t1.b desc limit 4;
   187  select * from t1 where t1.b in (select b from t2) order by t1.b desc limit 4;
   188  select * from t1 where t1.b not in (select b from t2) order by t1.b desc limit 4;
   189  drop table t1;
   190  drop table t2;