the sql joins clause is used to combine records from two or more tables in a database. a join is a means for combining fields from two tables by using values common to each.

consider the following two tables −

table 1 − customers table

+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+

table 2 − orders table

+-----+---------------------+-------------+--------+
|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, let us join these two tables in our select statement as shown below.

sql> select id, name, age, amount
   from customers, orders
   where  customers.id = orders.customer_id;

this would produce the following result.

+----+----------+-----+--------+
| id | name     | age | amount |
+----+----------+-----+--------+
|  3 | kaushik  |  23 |   3000 |
|  3 | kaushik  |  23 |   1500 |
|  2 | khilan   |  25 |   1560 |
|  4 | chaitali |  25 |   2060 |
+----+----------+-----+--------+

here, it is noticeable that the join is performed in the where clause. several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, between, like, and not; they can all be used to join tables. however, the most common operator is the equal to symbol.

there are different types of joins available in sql −

inner join − returns rows when there is a match in both tables.
left join − returns all rows from the left table, even if there are no matches in the right table.
right join − returns all rows from the right table, even if there are no matches in the left table.
full join − returns rows when there is a match in one of the tables.
self join − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the sql statement.
cartesian join − returns the cartesian product of the sets of records from the two or more joined tables.

let us now discuss each of these joins in detail.

Previous Lesson

Next Lesson