最頻繁使用的和重要的聯(lián)接是INNER JOIN。它也被稱為一個(gè)等值連接。
INNER JOIN通過(guò)結(jié)合基于聯(lián)接謂詞兩個(gè)表(表1和表2)列值創(chuàng)建一個(gè)新的結(jié)果表。查詢比較表1中的每一行與表2中的每一行,找到所有滿足聯(lián)接謂詞的行。 當(dāng)聯(lián)接謂詞被滿足時(shí),對(duì)于每個(gè)匹配的一對(duì)A和B的列值被組合成一個(gè)結(jié)果行。
INNER JOIN基本語(yǔ)法如下:
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;
考慮下面的兩個(gè)表中,(a)CUSTOMERS表如下:
+----+----------+-----+-----------+----------+ | 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 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
(b) 另一個(gè)ORDERS表命令如下:
+-----+---------------------+-------------+--------+ | OID | DATE | ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 | 3 | 3000 | | 100 | 2009-10-08 00:00:00 | 3 | 1500 | | 101 | 2009-11-20 00:00:00 | 2 | 1560 | | 103 | 2008-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+
現(xiàn)在,讓我們使用INNER JOIN這兩個(gè)表如下:
SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
這將產(chǎn)生以下結(jié)果:
+----+----------+--------+---------------------+ | ID | NAME | AMOUNT | DATE | +----+----------+--------+---------------------+ | 3 | kaushik | 3000 | 2009-10-08 00:00:00 | | 3 | kaushik | 1500 | 2009-10-08 00:00