MySQL Joins

MySQL Joins Example

MySQL joins: There are several tables stored in a database but sometimes need arises that we want to access data of more than one table in that case MySQL join is used . MySQL joins basically merge two or more than two tables together to form a single table that contains data of all tables . there are four types of join in MySQL, these are:
Note :Here Person is Left Table and Orders is Right Table

Inner join

if there is any common key(primary and foreign key) in both tables then the join so performed is called inner join. Query

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

Output

Left join

left join returns all rows from left table even if there is no match in the right table. Query

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
                  FROM Persons
                  LEFT JOIN Orders
                   ON Persons.P_Id=Orders.P_Id
                   ORDER BY Persons.LastName

Output

Right join

right join returns all rows from right table even if there is no match in the left table Query

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
                  FROM Persons
                  RIGHT JOIN Orders
                   ON Persons.P_Id=Orders.P_Id
                   ORDER BY Persons.LastName

Output

Outer join(Full Join)

outer join displays all rows from left table and right table. if there are rows in left table which does not matches with the rows in right table , those rows will also be displayed and viceversa. Query

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
                  FROM Persons
                  OUTER JOIN Orders
                   ON Persons.P_Id=Orders.P_Id
                   ORDER BY Persons.LastName

Output

How Joins more than two tables

select a.id,b.profle,c.salary from  contact a , profile b, salary c  where a.id=b.id=c.id;