Joins in MySQL

Joins

In a MySQL command, a join is used to select the data from two or more tables or views. In a join, we define multiple tables or views. 

Types of Joins :

• INNER Joins
• OUTER Joins
• SELF Joins

We have to discuss Join with the help of the following two tables :

INNER Joins

Inner Join is the most commonly used in applications .Inner Joins creates a new result table by the combination column values of two tables.
we can use this method to select certain fields from both tables and only if the correct rows will join together.

mysql> select * from company
-> INNER JOIN products
-> On company.c_id=products.c_id;

OUTER Joins

In outer Join,we the help of this in the two tables all the records from one table even
there is no corresponding record in other table.

There are two types of outer join :

1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN

LEFT OUTER JOIN

In the left outer join is used to return all the rows that returned by an inner join plus all
the rows from table that did not match with any row from the second table but with null values for
each column from second table.

mysql> select * from company
-> LEFT outer join products
-> On company.c_id=products.c_id;

Example : In the following example we are using Order BY with the LEFT OUTER Join.

mysql> SELECT * FROM company
-> LEFT OUTER JOIN Products
-> ON company.C_ID=Products.C_ID
-> ORDER BY company.c_address;

RIGHT OUTER JOIN
It is same as the LEFT OUTER Join but in the RIGHT OUTER Join used to return all the rows that
returned by an INNER Join plus all the rows from second table that did not match
any row from the first table but with NULL values for each column from first table.

mysql> SELECT * FROM company
-> RIGHT OUTER JOIN Products
-> ON company.C_ID=Products.C_ID;

SELF JOIN

SELF Join means that when a table can be joined with itself then SELF Join is usedto compare values
in a column to other values in the same column.

Example : In the following example for creating SELF Join a table twice in the FROM clause ans assign it a different alias each time.

mysql> SELECT * FROM company
-> WHERE c_address=(
-> SELECT c_address FROM company
-> WHERE C_ID=1);