SQL Joins



SQL Aliases :

  1. SQL Joins clause is used to combine records from two or more tables in a database.
  2. SQL Join is used to combine fields from fields from two tables by using values common to each other

Type of Join :

Different types of joins –

Type of JoinDescription
Inner joinAll the rows will be returned that are having match in both tables.
Left joinAll the rows will be returned from the left table even if there are no matches in the right table.
Right joinAll the rows will be returned from the right table even if there are no matches in the left table.
Full joinAll the rows will be returned that are having match in one of the tables.
Self joinIt is used to join itself by renaming the table with another name and pretending that there are two tables.
Cartesian joinAll the rows will be returned as the result of Cartesian product of the sets of records from the two or more joined tables.

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 :

Consider below query on demo database –

SQL> SELECT ID, NAME, AGE, AMOUNT
        FROM CUSTOMERS, ORDERS
        WHERE  CUSTOMERS.ID = ORDERS.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      |
+----+----------+-----+-----------+

In the above query we have joined two tables. Each record of CUSTOMER table will be checked with each record from the ORDER table like below –

Records CalculationCount
Total Number of Records in CUSTOMER7
Total Number of Records in ORDER4
Total Number of Comparisons28
Total Records Selected4