MySQL QUERY : JOIN OPERATION


Join is one of query operation on MySQL, there are three kinds of join operations in mysql query. 
that is: Inner Join, Left Join, and Right Join.
The following functions of the join query operations is

Inner Join, useful to combine several fields in two tables or more, with an same id on each table. Records result is same id between tables.

Left Join, useful to combine several fields in two tables or more, with an same id on each tables. by displaying all existing records in left side table, although records does not exist in right side tables and displaying empty field in the right side table if there is no records that equals with left table.

Right Join, the opposite function of the left join. Useful to combine several fields in the two tables or more, with an same id on each tables. by displaying all existing records in right side table, although records does not exist in left side tables and displaying empty fields in the left side table if there is no records that equals with right table.

See the following cases:

Table_1
ID NAME
1 RAMA
2 SARI
3 DIAN

Table_2
ID SCORE
1 300
2 200


INNER JOIN

MySQL Query :"SELECT * FROM Table_1 INNER JOIN Table_2 ON Table_1.ID = Table_2.ID"

Result
ID NAME ID SCORE
1 RAMA 1 300
2 SARI 2 200


MySQL Query :"SELECT Table_1.ID, Table_1.NAME, Table_2.SCORE FROM Table_1 INNER JOIN Table_2 ON Table_1.ID = Table_2.ID"

Result
ID NAME SCORE
1 RAMA 300
2 SARI 200


LEFT JOIN

MySQL Query : "SELECT * FROM Table_1 LEFT JOIN Table_2 ON Table_1.ID = Table_2.ID"

Result
ID NAME ID SCORE
1 RAMA 1 300
2 SARI 2 200
3 DIAN  (NULL) (NULL)

MySQL Query : "SELECT Table_1.ID, Table_1.NAME, Table_2.SCORE FROM Table_1 LEFT JOIN Table_2 ON Table_1.ID = Table_2.ID"

Result
ID NAME SCORE
1 RAMA 300
2 SARI 200
3 DIAN  (NULL)


RIGHT JOIN

MySQL Query : "SELECT * FROM Table_1 RIGHT JOIN Table_2 ON Table_1.ID = Table_2.ID"

Result
ID NAME ID SCORE
1 RAMA 1 300
2 SARI 2 200

MySQL Query : "SELECT Table_1.ID, Table_1.NAME, Table_2.SCORE FROM Table_1 RIGHT JOIN Table_2 ON Table_1.ID = Table_2.ID"

Result
ID NAME SCORE
1 RAMA 300
2 SARI 200




EmoticonEmoticon