SQL Inner Joins



SQL Inner Joins :

  1. The SQL INNER JOIN keyword is used to select all records from both tables as long as there is a match between the columns in both tables.
  2. SQL INNER Join is also called as EQUIJOIN
  3. SQL INNER JOIN creates a new result table by combining the two tables (table1 and table2).
  4. SQL INNER JOIN combines two table using join predicate where each row of table1 is compared with each row of table2
  5. If the where clause is satisfied then column values for each matched pair of rows of A and B are combined into a result row.

SQL Inner Joins : Venn Represenation

sql-inner-jon

Demo Database :

Consider the following demo table Called – “CUSTOMER”

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Ram      |  21 | Karachi   |  2000.00 |
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |
|  5 | Poonam   |  29 | Bhopal    |  8500.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
|  7 | Arnav    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

and following table “ORDER”

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2011-11-02 00:00:00 |           3 |   2000 |
| 100 | 2011-11-02 00:00:00 |           3 |   3500 |
| 101 | 2011-11-24 00:00:00 |           2 |   4500 |
| 103 | 2011-03-20 00:00:00 |           4 |   5000 |
+-----+---------------------+-------------+--------+

SQL Joins Clause :

SQL Inner join can be syntactically written as –

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

OR

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Now Consider below query on demo database –

SQL> SELECT  ID, ENAME, AGE, AMOUNT
     FROM CUSTOMER
     INNER JOIN ORDER
     ON CUSTOMER.ID = ORDER.CUSTOMER_ID;

will result into

+----+----------+-----+-----------+
| ID | ENAME    | AGE | AMOUNT    |
+----+----------+-----+-----------+
|  3 | Ram      |  21 | 2000      |
|  3 | Ram      |  21 | 3500      |
|  2 | Raj      |  23 | 4500      |
|  4 | Saurabh  |  25 | 5000      |
+----+----------+-----+-----------+