JOIN

In each situation that we have faced so far, the data was extracted from a single table.
There was no need to refer to more than one tables in the same statement. But many
times, in real applications of databases, it is required to produce reports which need data
from more than one tables.

An SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each

 To understand how this is done, consider the following tables of a database.
+------+-------------+
| Code | Name |
+------+-------------+
| P001 | Toothpaste |
| P002 | Shampoo |
| P003 | Conditioner |

Supplier
Order_table
+----------+--------------+-------------+
| Sup_Code | Name | Address |
+----------+--------------+-------------+
| S001 | DC & Company | Uttam Nagar |
| S002 | SURY Traders | Model Town |
+----------+--------------+-------------+
+----------+--------+----------+
| Order_No | P_Code | Sup_Code |
+----------+--------+----------+
| 1 | P001 | S002 |
| 2 | P002 | S002 |
+----------+--------+----------+

Cartesian product (also called Cross Join) of two tables is a table obtained by pairing up
each row of one table with each row of the other table. This way if two tables contain 3
rows and 2 rows respectively, then their Cartesian product will contain 6 (=3x2) rows. This
can be illustrated as follows:
In SQL, Cartesian product of two rows is obtained by giving the names of both tables in FROM clause. An example of Cartesian product is shown below:

SELECT * FROM order_table, product;

To give the output of this query, MySQL will pair the rows of the mentioned tables as
follows:
+----------+--------+----------+                 +------+-------------+
| Order_No | P_Code | Sup_Code                     | | Code | Name |
+----------+--------+----------+                  +------+-------------+
| 1 | P001 | S002 | |                                               P001 | Toothpaste || | | |
| 2 | P002 | S002 | |                                               P002 | Shampoo |
+----------+--------+----------+                          P003 | Conditioner |

And the following output will be produced:
+----------+--------+----------+------+-------------+
| Order_No | P_Code | Sup_Code | Code | Name |
+----------+--------+----------+------+-------------+
| 1 | P001 | S002 | P001 | Toothpaste |
| 2 | P002 | S002 | P001 | Toothpaste |
| 1 | P001 | S002 | P002 | Shampoo |
| 2 | P002 | S002 | P002 | Shampoo |
| 1 | P001 | S002 | P003 | Conditioner |
| 2 | P002 | S002 | P003 | Conditioner |
+----------+--------+----------+------+-------------+
Here we observe that the Cartesian product contains all the columns from both tables.
Each row of the first table (Order_table) is paired with each row of the second table.


EQUI JOIN OF THE TABLES

The complete Cartesian product of two or more tables is, generally, not used directly.
Sometimes the complete Cartesian product of two tables may give some confusing
information also. For example, the first Cartesian product (CP-1) indicates that each
order (Order Numbers 1 and 2) is placed for each Product (Code 'P001', 'P002', 'P003'). But
this is incorrect!
.
But we can extract meaningful information from the Cartesian product by placing some
conditions in the statement. For example, to find out the product details corresponding
to each Order details, we can enter the following statement:

SELECT * FROM order_table, product WHERE p_code = code;

+----------+--------+----------+------+------------+
| Order_No | P_Code | Sup_Code | Code | Name |
+----------+--------+----------+------+------------+
| 1 | P001 | S002 | P001 | Toothpaste |
| 2 | P002 | S002 | P002 | Shampoo |
+----------+--------+----------+------+------------+
Two table names are specified in the FROM clause of this statement, therefore MySQL
creates a Cartesian product of the tables. From this Cartesian product MySQL selects only
those records for which P_Code (Product code specified in the Order_table table)
matches Code (Product code in the Product table). These selected records are then
displayed.

EQUI JOIN OF THREE TABLES

Select Order_no, Product.name as Product, Supplier.Name as Supplier

From order_table, Product, Supplier

WHERE order_table.Sup_Code = Supplier.Sup_Code

and P_Code = Code;

The output produced by this statement is:

+----------+------------+--------------+

| Order_no | Product | Supplier |

+----------+------------+--------------+

| 1 | Toothpaste | SURY Traders |

| 2 | Shampoo | SURY Traders |

+----------+------------+--------------+