MySQL Joins

Mysql Inner Join returns the set of  only those records which matches in one table with another.
If I do a regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT),
then I get all records that match in the appropriate way in the two tables,
and records in both incoming tables that do not match are not reported:

mysql> select name, phone, selling from demo_people join demo_property
            on demo_people.pid = demo_property.pid;

If I do a LEFT JOIN, I get all records that match in the same way and
IN ADDITION I get an extra record for each unmatched record
in the left table of the join - thus ensuring (in following example) that every
 PERSON gets a mention ie from demo_people table:

mysql> select name, phone, selling from demo_people left join demo_property    
    on demo_people.pid = demo_property.pid;    
   
If I do a RIGHT JOIN, I get all the records that match and
IN ADDITION I get an extra record for each unmatched record in the right table
 of the join - in following example, that means that each property (from demo_property )
gets a mention even if we don't have seller details.

mysql> select name, phone, selling from demo_people right join demo_property
           on demo_people.pid = demo_property.pid;

Mysql Full Join is used to return all the records from both left and right outer join.
The joined table contain all records from both tables and fill nulls values
for those missing matches on either side.
The Full Join in Mysql is the outcome result set of left outer join and right outer join using UNION clause.

mysql>  select * from Roseindia as R left outer join newstrack as N
    -> on  R.empid=n.empid
    -> Union
    -> select * from Roseindia as R right outer join newstrack N
    -> on R.empid=n.empid;

An INNER JOIN does a full join, just like the first example, and
the word OUTER may be added after the word LEFT or RIGHT in the 2nd & 3rd examples -
it's provided for ODBC compatibility and doesn't add an extra capabilities.  

Mysql Natural Join is a specialization of equi-joins. The join compares all columns in
both tables that have the same column-name in both tables that have column name in
the joined table. The resulting set include only one column for each pair of the same named column.
More Info http://www.roseindia.net/sql/sqljoin/mysql-natural-join.shtml

CROSS JOIN:This type of join is the simplest join. The cross join result in cartesian product of all the records from two tables
mysql> select  * from roseindia cross  join newstrack;

INNER JOIN OR EQUI JOIN:This is the type of join where tables are combined based on a common column.
OUTER JOIN: Join is used to combine all rows of one table with  matching rows from the other table and also show unmatchable records from other table. It is used whenever multiple tables must be accessed through a SQL SELECT statement.

http://www.roseindia.net/sql/sqljoin/mysql-join-query.shtml

Sources:
http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html
http://www.roseindia.net/sql/sqljoin/mysql-full-join.shtml
For more info:
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

Tags:MySQL,SQL Joins,