Use Join Table

Why we need query join? Query join use to join some table become one table. Technic to join is plus value from (RDBMS: Relational Database Management System), where data can keep in some little different table, not one big table with many column. In Programing, query join almost always use, cause that is the key to produce more valuable information than one query in one table.

How it works is to join a match with the search for a particular column in a table with a column in another table, and displays the results as a set of aggregate data. In general, there are 3 types of join, the inner JOIN, LEFT JOIN and RIGHT JOIN. However, the addition of 3 kinds, there are several additional variants depending on the RDBMS used.

For example, we will join the table that store patient information Bios patients, the symptom that the table data store symptom suffered by patients. Both tables will be based on a join-column containing the patient's identity number (id) that is located on the second table.

CREATE TABLE pasien (id int(10), nama char(30));
INSERT INTO pasien (id,nama) VALUES ('1','John');
INSERT INTO pasien (id,nama) VALUES ('2','Jane');
INSERT INTO pasien (id,nama) VALUES ('3','Mike');
INSERT INTO pasien (id,nama) VALUES ('4','Doel');

CREATE TABLE symptom (id int(10), symptom char(30));
INSERT INTO symptom (id,symptom) VALUES ('1','Demam');
INSERT INTO symptom (id,symptom) VALUES ('2','Pusing');
INSERT INTO symptom (id,symptom) VALUES ('3','Mual');

INNER JOIN

With INNER JOIN

only

shown that these lines to one another have a match.

Sample:

SELECT a.nama, b.symptom
FROM pasien a
INNER JOIN symptom b
ON b.id = a.id;
+------+---------+
| nama | symptom |
+------+---------+
| John | Demam   |
| Jane | Pusing  |
| Mike | Mual    |
+------+---------+

LEFT JOIN

A query with LEFT JOIN akan show

all line

from the left side of the table (first / main or patient) combined with the right side of the table (second or symptom).

Sample:

SELECT a.nama, b.symptom
FROM pasien a
LEFT JOIN symptom b
ON b.id = a.id;
+------+---------+
| nama | symptom |
+------+---------+
| John | Demam   |
| Jane | Pusing  |
| Mike | Mual    |
| Doel | NULL    |
+------+---------+

The difference with the inner JOIN is on LEFT JOIN - the entire first row of the table will be displayed even if no matching rows in both tables. See the above example, the patient did not have data Doel symptom so shown NULL (NULL here states that there is no data in the column). In addition, the position of the table so have the meaning, the first table or the left (before the table is' LEFT JOIN ') will be the main table that quering first, then finding matches with the right table or the second (after the table is' LEFT JOIN') .

RIGHT JOIN

RIGHT JOIN query is almost the same as the LEFT JOIN, just reversed its position, which will display all rows from the right table (the second) although there is no match on the left of the table (the first).

Sample:

SELECT a.nama, b.symptom
FROM pasien a
RIGHT JOIN symptom b
ON b.id = a.id;
+------+---------+
| nama | symptom |
+------+---------+
| John | Demam   |
| Jane | Pusing  |
| Mike | Mual    |
+------+---------+