Sunday 22 March 2015

Difference between inner join and outer join in MySQL

Difference between inner join and outer join in MySQL


Joins are used to get the records from two OR more tables. Following are different type of joins  used in mysql.
  • Left Join
  • Right Join
  • Inner Join
  • Self Join
  • Outer Join


Following are difference between Inner Join & Outer Join.

Inner Join: It is used to get the common records from two table or more tables.

MySQL Inner Join Example:
SELECT * FROM `geo_cities` as gc INNER JOIN meta_data AS md on md.city_id=gc.id;


Outer Join: It is used to get the all records from two tables or more tables.
MySQL Outer Join Example:
MySQL does not provide the FULL JOIN.
You need to UNION the result of Right Join and Left Join from both tables.
SELECT * FROM `geo_cities` as gc LEFT JOIN meta_data AS md on md.city_id=gc.id
UNION
SELECT * FROM `geo_cities` as gc RIGHT JOIN meta_data AS md on md.city_id=gc.id