INNER JOIN

功能描述

在MySQL兼容模式下支持INNER JOIN连接条件使用WHERE子句。

注意事项

• 该功能仅在数据库兼容模式为MySQL时能够使用（即创建DB时DBCOMPATIBILITY='B'），在其他数据库兼容模式下不能使用该特性。

• 仅Vastbase G100 V2.2 Build 10(Patch No.7)及以后版本支持此功能。

语法格式

SELECT col_name FROM table1 INNER JOIN table2 WHERE condition

参数说明

• col_name

  字段名。

• table1、table2

  内连接的表名。

• condition

  条件表达式。

示例

1、创建兼容MySQL的库db_mysql，并进入。

CREATE DATABASE db_mysql dbcompatibility='B';
\c db_mysql

2、创建测试表并插入数据。

create table test1(id int,name varchar(10),score numeric,date1 date,c1 bytea);
insert into test1 values(1,'aaa',97.1,'1999-12-12','0101');
insert into test1 values(5,'bbb',36.9,'1998-01-12','0110');
insert into test1 values(3,'ccc',89.2,'2200-03-12','0111');
insert into test1 values(7,'uuu',99.9,'1987-01-01','1000');
insert into test1 values(30,'ooo',90.1,'2023-01-30','1001');
insert into test1 values(6,'hhh',60,'2022-12-22','1010');
insert into test1 values(7,'fff',71,'2001-11-23','1011');
insert into test1 values(-1,'yaya',77.7,'2008-09-10','1100');
insert into test1 values(7,'fff',71,'2001-11-23','1011');  
create table test2(id int,name varchar(10),score numeric,date1 date,c1 bytea);
insert into test2 values(1,'aaa',99.1,'1998-12-12','0101');
insert into test2 values(2,'hhh',36.9,'1996-01-12','0110');
insert into test2 values(3,'ddd',89.2,'2000-03-12','0111');
insert into test2 values(7,'uuu',60.9,'1997-01-01','1000');
insert into test2 values(9,'ooo',90.1,'2021-01-30','1001');
insert into test2 values(6,'hhh',90,'2022-10-22','1010');
insert into test2 values(11,'eee',71,'2011-11-20','1011');
insert into test2 values(-1,'yaya',76.7,'2008-09-10','1100');
insert into test2 values(7,'uuu',60.9,'1997-01-01','1000');

3、使用where指定连接条件查询数据。

select * from test1 inner join test2 where test1.id=test2.id and test1.score>60;

结果返回如下：

 id | name | score |   date1    |     c1     | id | name | score |   date1    |     c1
----+------+-------+------------+------------+----+------+-------+------------+------------
  1 | aaa  |  97.1 | 1999-12-12 | \x30313031 |  1 | aaa  |  99.1 | 1998-12-12 | \x30313031
  3 | ccc  |  89.2 | 2200-03-12 | \x30313131 |  3 | ddd  |  89.2 | 2000-03-12 | \x30313131
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | uuu  |  99.9 | 1987-01-01 | \x31303030 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
 -1 | yaya |  77.7 | 2008-09-10 | \x31313030 | -1 | yaya |  76.7 | 2008-09-10 | \x31313030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | uuu  |  99.9 | 1987-01-01 | \x31303030 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
(9 rows)

4、缺省INNER关键字查询数据。

select * from test1 join test2 where test1.date1=test2.date1;

结果返回如下：

 id | name | score |   date1    |     c1     | id | name | score |   date1    |     c1
----+------+-------+------------+------------+----+------+-------+------------+------------
 -1 | yaya |  77.7 | 2008-09-10 | \x31313030 | -1 | yaya |  76.7 | 2008-09-10 | \x31313030
(1 row)

5、查询具体列值。

select test1.score from test2 join test1 where test2.id=test1.id and test2.name='yaya';

结果返回如下：

 score
-------
  77.7
(1 row)

6、使用distinct关键字返回唯一不同的值。

select distinct test1.id from test1 inner join test2 where test1.id=test2.id;

结果返回如下：

 id
----
  1
  3
  6
  7
 -1
(5 rows)

7、使用order by语句。

select * from test1 inner join test2 where test1.id=test2.id order by 1,2;

结果返回如下：

 id | name | score |   date1    |     c1     | id | name | score |   date1    |     c1
----+------+-------+------------+------------+----+------+-------+------------+------------
 -1 | yaya |  77.7 | 2008-09-10 | \x31313030 | -1 | yaya |  76.7 | 2008-09-10 | \x31313030
  1 | aaa  |  97.1 | 1999-12-12 | \x30313031 |  1 | aaa  |  99.1 | 1998-12-12 | \x30313031
  3 | ccc  |  89.2 | 2200-03-12 | \x30313131 |  3 | ddd  |  89.2 | 2000-03-12 | \x30313131
  6 | hhh  |    60 | 2022-12-22 | \x31303130 |  6 | hhh  |    90 | 2022-10-22 | \x31303130
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | fff  |    71 | 2001-11-23 | \x31303131 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | uuu  |  99.9 | 1987-01-01 | \x31303030 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
  7 | uuu  |  99.9 | 1987-01-01 | \x31303030 |  7 | uuu  |  60.9 | 1997-01-01 | \x31303030
(10 rows)