Joins

Sometimes you required the data from more than one table. When you select the data from more than one table this is known as Joining. A join is a SQL query that is used to select the data from more than one table or views.

Sometimes you required the data from more than one table. When you select the data from more than one table this is known as Joining. A join is a SQL query that is used to select the data from more than one table or views.

Joins

Sometimes you required the data from more than one table. When you select the
data from more than one table this is known as Joining. A join is a SQL query that is used to select the data from more than one table or views. When you define multiple tables or views in the FROM clause of a query the MySQL performs a join that linking the rows from multiple tables together.

Types of Joins :

INNER Joins

OUTER Joins

SELF Joins

We are going to describe you the Join with the help of following two tables :

The INNER join is considered as the default Join type. Inner join returns the column values from one row of a table combined with the column values from one row of another table that satisfy the search condition for the join.
The general syntax of INNER Join is :SELECT <column_name1>, <column_name2> FROM <tbl_name> INNER JOIN <tbl_name> ON <join_conditions>

The following example takes all the records from table Client and finds the matching records in table Product. But if no match is found then the record from table Client is not included in the results. But if multiple results are found in table Product with the given condition then one row will be return for each.
Example :

Sometimes when we are performing a Join between the two tables, we need all the records from one table even there is no corresponding record in other table. We can do this with the help of OUTER Join.
In other words an OUTER Join returns the all rows that returned by an INNER Join
plus all the rows from one table that did not match any row from the other
table. Outer Join are divided in two types :LEFT
OUTER Join, RIGHT OUTER Join

LEFT OUTER Join

LEFT OUTER Join is used to return all the rows that returned by an INNER Join plus all the rows from first table that did not match with any row from the second table but with the NULL values for each column from second table.
The general syntax of LEFT OUTER Join is :SELECT <column_name1>, <column_name2> FROM <tbl_name> LEFT OUTER JOIN <tbl_name> ON <join_conditions>

In the following example we are selected every row from the Client table which don?t have a match in the Products Table.
Example :

In the result of LEFT OUTER Join " R S P Ltd
" is included even though it has no rows in the Products table.

RIGHT OUTER Join

RIGHT OUTER Join is much same as the LEFT OUTER JOIN. But RIGHT OUTER Join is used to return all the rows that returned by an INNER Join plus all the rows from second table that did not match with any row from the first table but with the NULL values for each column from first table.
The general syntax of RIGHT OUTER Join is :SELECT <column_name1>, <column_name2> FROM <tbl_name> RIGHT OUTER JOIN <tbl_name> ON <join_conditions>

In the following example we are selected every row from the
Products table which don?t have a match in the Client Table.
Example :

SELF Join means a table can be joined with itself. SELF Join is useful when we want to compare values in a column to other values in the same column. For creating a
SELF Join we have to list a table twice in the FROM clause and assign it a different alias each time. For referring the table we have to use this aliases.

The following example provide you the list of those
Clients that belongs to same city of C_ID=1.