MySQL : Join Query

Cause lot of task in the office lately so I haven't had as much time to post any note, so here I would like to make a note about Join Query in MySQL.

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. Tables in a database are often related to each other with keys. So we can get data easier to read as what we want.

Assume that we have table and we'll try to make a relation, we make a simple table which have structure as below :

Table user:

CREATE TABLE `user` (
`id_user` int(11) NOT NULL auto_increment,
`name` varchar(20) NOT NULL,
PRIMARY KEY  (`id_user`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Table user_description :

CREATE TABLE `user_description` (
`id_user` int(11) NOT NULL,
`address` varchar(50) NOT NULL,
`telephone` varchar(15) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Data :

INSERT INTO `user` (`id_user`, `name`) VALUES
(1, 'anto purwanto'),
(2, 'johnny'),
(3, 'kartikarina');
INSERT INTO `user_description` (`id_user`, `address`, `telephone`) VALUES
(1, 'cimahi', '082550435544'),
(2, 'cibiru', '022 7823413'),
(4, 'tubagus ismail', '022 66224325');

1. Inner Join

Inner Join or EQUIJOIN is a clause that have function to get all row from the origin table and destination table where it just will fetch value from the key which just have ​​associated conditions. If there's value, so show the row (data) otherwise it will throw nothing.
INNER keyword could be used explicitly or not. if we do not use it explicitly, so join construction without other keyword is considered as INNER JOIN.

example of non explicit :

SELECT *
FROM user u, user_description ud
WHERE u.id_user = ud.id_user

using INNER Keyword :

SELECT *
FROM user u INNER JOIN user_description ud
WHERE u.id_user = ud.id_user

both of queries will make result to be like this :

id_user	name	id_user	address	telephone
1	anto purwanto	1	cimahi	082550435544
2	johnny	2	cibiru	022 7823413

Note : if we make table relation without a condition, it will cause, every row from the left reference table would be combined with all row from the right table which is related.

2. CROSS JOIN

Cross join is identical with inner join in MySQL 5.0. So the explanation about it is same as inner join. we don't have to talk it again. :D

example :

SELECT *
FROM user u CROSS JOIN user_description ud
WHERE u.id_user = ud.id_user

3. OUTER JOIN

Outer join is one of join type which looking for data reference from a source table to other table where this join wouldn't eliminate source data if the reference not found.

Note :

- We have to able distinguish between source table and reference table, basically we have to know and specify position of source table if source table is on the (LEFT) or (RIGHT).

- if there's no data from reference table which matching with the join condition then the data from source table would be shown but columns from reference table will just contain null.

Let's prove it, for example we want to display all user table and it will be shown although there's no match data in user_description table .

SELECT *
FROM user u LEFT OUTER JOIN user_description ud
ON u.id_user = ud.id_user

Query above will display table like this :

id_user	name	id_user	address	telephone
1	anto purwanto	1	cimahi	082550435544
2	johnny	2	cibiru	022 7823413
3	kartikarina	NULL	NULL	NULL

Actually, we could just set LEFT JOIN without OUTER to fetch the data. The concept is just I mentioned before, we have to know position of the table. Consider between left or right the position of table. You could try with RIGHT JOIN, it will show NULL if there's no matching data from the reference table

4. STRAIGHT JOIN

Straight join is a replacement for the MySQL JOIN keyword which used to "force" the process of table join from left (LEFT) to right (RIGHT).

SELECT *
FROM user u STRAIGHT_JOIN user_description ud
ON u.id_user = ud.id_user

5. NATURAL JOIN

If we pay attention about usage of LEFT and RIGHT join, it will display columns that have redundancy so the function of NATURAL JOIN is to delete column that have redundancy when the process of relation.

SELECT *
FROM user NATURAL JOIN user_description 

id_user	name	id_user	address	telephone
1	anto purwanto	1	cimahi	082550435544
2	johnny	2	cibiru	022 7823413

and query below will generate same output with above query that using NATURAL JOIN :

SELECT *
FROM user u JOIN user_description ud USING (id_user)

That's all a little note about MySQL, you can using any of JOIN for fetch your data.

Reference :

1. http://mysql.phi-integration.com/sql/tipe-tipe-join-pada-mysql-5
2. http://khalifavi.wordpress.com/2009/01/08/join-table-di-mysql/