Tag Archives: MySQL – FULL JOIN Example

March 7, 2015 Sql No comments

SQL – FULL JOIN

SQL -FULL JOIN

SQL – FUll JOIN `selects all rows from left table and the right table. It Returns both left & Right Join’s data together.

Syntax : SQL – FULL JOIN

SELECT LeftTable.ColumnName1,LeftTable.ColumnName2…RightTable.ColumnName1,RightTable.ColumnName2… FROM LeftTable FULL JOIN RightTable ON (LeftTable.ColumnName = RightTable.ColumnName);

We Have The Following Tables :

Orders Table :

Note : “UserId” In “Orders” Table is the id of the users which is assigned to users in “Users” Table in “ID” column. The Values stored in “UserId” Column are reffered from “Users” Table.

And

Users Table :

Left Join Example Can Be Explained Using The Above Two Tables :

Example

SELECT Users.Name,Users.Email,Orders.OrderItem FROM Users Full JOIN Orders ON (Users.ID= Orders.UserId);

Example

SELECT Users.Name,Users.Email,Orders.OrderItem FROM Users Full OUTER JOIN Orders ON (Users.ID= Orders.UserId);

Note : Few Databases do not support FULL JOIN.

For Those databases which do not support full join you can use following method :

Example

SELECT Users.Name,Users.Email,Orders.OrderItem FROM Users LEFT JOIN Orders ON (Users.ID= Orders.UserId)
UNION ALL
SELECT Users.Name,Users.Email,Orders.OrderItem FROM Users RIGHT JOIN Orders ON (Users.ID= Orders.UserId);

The Above Left Join Will Produce :