MySQL - JOIN

The MySQL JOIN clause is used to combine rows of two or more tables based on common column between them. There are four types of JOINs in MySQL:

• INNER JOIN: It is sometimes called simple JOIN. It returns records based on matching rows in both tables.
• LEFT JOIN: It is sometimes called LEFT OUTER JOIN. It returns records which contains all rows from left table and matching rows from right tables.
• RIGHT JOIN: It is sometimes called RIGHT OUTER JOIN. It returns records which contains all rows from right table and matching rows from left tables.
• CROSS JOIN: Returns records which contains all rows from both tables. It is sometimes called CARTESIAN JOIN because in the absence of a WHERE condition it behaves like a CARTESIAN PRODUCT i.e., the number of rows in the result-set is the product of the number of rows of the two tables.

Example:

Consider a database containing tables called Employee and Contact_Info with the following records:

Table 1: Employee table

Table 2: Contact_Info table

• The JOIN clause is used with Employee and Contact_Info tables based on common column EmpID. It returns Name, Age and Address columns based on match in both tables. The query is given below:

  SELECT Employee.Name, Employee.Age, Contact_Info.Address 
  FROM Employee
  JOIN Contact_Info
  ON Employee.EmpID = Contact_Info.EmpID;
  

  
  

  This will produce the result as shown below:

  Name	Age	Address
  Marry	24	Brooklyn, New York, USA
  Jo	27	Grenelle, Paris, France
  Kim	30	Geuzenveld, Amsterdam, Netherlands
  Huang	28	Yizhuangzhen, Beijing, China