SELECT ... INNER JOIN

---

SELECT Query with INNER JOIN

The INNER join is the most important and frequently used among all joins.This is also referred as EQUIJOIN

Syntax

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Example

Consider the following two tables

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ranith  |  32 | Ahmedabad |  2000.00 |
|  2 | Krupa   |  25 | Delhi     |  1500.00 |
|  3 | kaulik  |  23 | Kota      |  2000.00 |
|  4 | Chaitan |  25 | Mumbai    |  6500.00 |
|  5 | Munny   |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Table 2 − ORDERS Table is as follows.

+-----+---------------------+-------------+--------+
| OID | DATE                | CUSTOMER_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 |
+-----+---------------------+-------------+--------+

Now, using the INNER JOIN let us join these two tables as follows

SQL> SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   INNER JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result.

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
|  3 | kaulik  |   3000 | 2009-10-08 00:00:00  |
|  3 | kaulik  |   1500 | 2009-10-08 00:00:00  |
|  2 | Krupa   |   1560 | 2009-11-20 00:00:00  |
|  4 | Chaitan |   2060 | 2008-05-20 00:00:00  |
+----+----------+--------+------+--------------+