INNER JOIN通过基于连接断言组合两个表(table1和table2)的列值来创建新的输出表,该查询将table1的每一行与table2的每一行进行比较,以找到满足join的所有行,当满足连接条件时,A和B的每个匹配行对的列值将合并为输出行。
INNER JOIN 的基本语法如下。
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_field=table2.common_field;
请考虑以下两个表。
表1 - 客户表(CUSTOMERS)如下。
链接:https://www.learnfk.comhttps://www.learnfk.com/sql/sql-inner-joins.html
来源:LearnFk无涯教程网
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Learnfk | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
表2 -订单表(ORDERS)如下。
+-----+---------------------+-------------+--------+ | OID | DATE | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2019-10-08 00:00:00 | 3 | 3000 | | 100 | 2019-10-08 00:00:00 | 3 | 1500 | | 101 | 2019-11-20 00:00:00 | 2 | 1560 | | 103 | 2018-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+
现在,让无涯教程使用INNER JOIN联接这两个表,如下所示:
SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID=ORDERS.CUSTOMER_ID;
这将产生以下输出。
+----+----------+--------+---------------------+ | ID | NAME | AMOUNT | DATE | +----+----------+--------+---------------------+ | 3 | kaushik | 3000 | 2019-10-08 00:00:00 | | 3 | kaushik | 1500 | 2019-10-08 00:00:00 | | 2 | Khilan | 1560 | 2019-11-20 00:00:00 | | 4 | Chaitali | 2060 | 2018-05-20 00:00:00 | +----+----------+--------+---------------------+
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)