What is Join?

Join is method to display more than one table data combaine with the help of commen relational field. Most of the coder think so it require to be set Primary key and foreign key Constraint by it not truth. Join require relational field between tables if we can not define relational column query is execute by it one field interect with other tabale all field.

Example

mysql> SELECT student.fname, mark.total FROM mark, student WHERE student.id = mark.id;
+----------+--------+
| fname    | total  |
+----------+--------+
| aman     | 283.00 |
| geeta    | 228.00 |
| sheena   |  99.00 |
| scarlit  | 129.00 |
| richelle | 169.00 |
| monika   | 168.00 |
+----------+--------+
6 rows in set (0.00 sec)

mysql>

INNER JOIN

The inner join is set select the related column of the both table.

Syntax

SELECT table_name.column_name FROM table_name INNER JOIN table_name ON relation_condition;

Example

mysql> SELECT s.fname,m.total FROM mark AS m INNER JOIN student AS s ON s.id = m.id;
+----------+--------+
| fname    | total  |
+----------+--------+
| aman     | 283.00 |
| geeta    | 228.00 |
| sheena   |  99.00 |
| scarlit  | 129.00 |
| richelle | 169.00 |
| monika   | 168.00 |
+----------+--------+
6 rows in set (0.00 sec)

mysql>

CROSS JOIN

The cross join is set select the not related column of the both table.

Syntax

SELECT table_name.column_name FROM table_name CROSS JOIN table_name ON relation_condition

Example

mysql> SELECT s.fname,m.m1,m.m2,m.m3 FROM mark AS m CROSS JOIN student AS s ON s.id = m.id;
+----------+------+------+------+
| fname    | m1   | m2   | m3   |
+----------+------+------+------+
| aman     |   99 |   93 |   91 |
| geeta    |   69 |   89 |   70 |
| sheena   |   10 |   39 |   50 |
| scarlit  |   40 |   59 |   30 |
| richelle |   60 |   69 |   70 |
| monika   |   60 |   69 |   99 |
+----------+------+------+------+
6 rows in set (0.00 sec)

mysql>