内联接查询

内联接（INNER JOIN）是两个或多个表的联接，用于返回满足联接条件的多个表数据。内联接又称为简单联接。

内联接语法

内联接默认 JOIN 返回的结果会满足 ON 后面的联接条件，通常会省略关键字 INNER。JOIN 前后的表分别称之为左表和右表，ON 里的条件描述的是左表和右表的联接条件和过滤条件。

内联接的 SQL 语法格式如下：

SELECT select_list FROM table_name1 [INNER] JOIN table_name2 ON join_condition
[ WHERE query_condition ]
[ ORDER BY column_list ]

如果同时使用 WHERE 和 ORDER BY 子句，则SQL 返回的结果会在 JOIN 结果的基础上再经过 WHERE 后的查询条件进行过滤，以及根据 ORDER BY 后的列排序进行结果排序。

如果没有 ON 子句，那么 INNER JOIN 返回的就是左表和右表的全部数据，称为笛卡儿积。

相同的联接条件下，ON 子句也可以由 WHERE 子句代替实现内联接查询。语法如下：

 SELECT select_list FROM table_name1，table_name2 [ WHERE query_condition ]

示例

创建示例表并插入适当数据。

obclient> CREATE TABLE tbl1(id NUMBER NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(id NUMBER NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected

obclient> INSERT INTO tbl1 VALUES(1,'A1'),(2,'B1'),(4,'D1'),(6,'F1'),(8,'H1'),(10,'J1');
Query OK, 6 rows affected
Records: 6  Duplicates: 0  Warnings: 0

obclient> INSERT INTO tbl2 VALUES(1,'B2'),(3,'C2'),(6,'F2'),(9,'I2');
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

带有联接条件示例

obclient> SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 JOIN tbl2 ON tbl1.id=tbl2.id;
+----+------+----+------+
| ID | NAME | ID | NAME |
+----+------+----+------+
|  1 | A1   |  1 | B2   |
|  6 | F1   |  6 | F2   |
+----+------+----+------+
2 rows in set

obclient> SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 INNER JOIN tbl2 ON tbl1.id=tbl2.id;
+----+------+----+------+
| ID | NAME | ID | NAME |
+----+------+----+------+
|  1 | A1   |  1 | B2   |
|  6 | F1   |  6 | F2   |
+----+------+----+------+
2 rows in set

obclient> SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1,tbl2 WHERE tbl1.id=tbl2.id; 
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
|  1 | A1   |  1 | B2   |
|  6 | F1   |  6 | F2   |
+----+------+----+------+
2 rows in set

无联接条件示例

obclient> SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 INNER JOIN tbl2;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
|  1 | A1   |  1 | B2   |
|  1 | A1   |  3 | C2   |
|  1 | A1   |  6 | F2   |
|  1 | A1   |  9 | I2   |
|  2 | B1   |  1 | B2   |
|  2 | B1   |  3 | C2   |
|  2 | B1   |  6 | F2   |
|  2 | B1   |  9 | I2   |
|  4 | D1   |  1 | B2   |
|  4 | D1   |  3 | C2   |
|  4 | D1   |  6 | F2   |
|  4 | D1   |  9 | I2   |
|  6 | F1   |  1 | B2   |
|  6 | F1   |  3 | C2   |
|  6 | F1   |  6 | F2   |
|  6 | F1   |  9 | I2   |
|  8 | H1   |  1 | B2   |
|  8 | H1   |  3 | C2   |
|  8 | H1   |  6 | F2   |
|  8 | H1   |  9 | I2   |
| 10 | J1   |  1 | B2   |
| 10 | J1   |  3 | C2   |
| 10 | J1   |  6 | F2   |
| 10 | J1   |  9 | I2   |
+----+------+----+------+
24 rows in set

组合条件查询示例

obclient> SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 INNER JOIN tbl2 ON tbl1.id=tbl2.id AND tbl1.id=6;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
|  6 | F1   |  6 | F2   |
+----+------+----+------+
1 row in set

obclient> SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 INNER JOIN tbl2 ON tbl1.id=tbl2.id WHERE tbl1.id=6;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
|  6 | F1   |  6 | F2   |
+----+------+----+------+
1 row in set

obclient> SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1,tbl2 WHERE tbl1.id=tbl2.id AND tbl1.id=6;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
|  6 | F1   |  6 | F2   |
+----+------+----+------+
1 row in set

obclient> SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 INNER JOIN tbl2 ON tbl1.id=tbl2.id ORDER BY tbl1.id DESC;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
|  6 | F1   |  6 | F2   |
|  1 | A1   |  1 | B2   |
+----+------+----+------+
2 rows in set