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 :

mysql> SELECT * FROM Client;
+------+---------------+----------+
| C_ID | Name          | City     |
+------+---------------+----------+
| 1    | A K Ltd       | Delhi    |
| 2    | V K Associate | Mumbai   |
| 3    | R K India     | Banglore |
| 4    | R S P Ltd     | Kolkata  |
+------+---------------+----------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM Products;
+---------+-------------+------+
| Prod_ID | Prod_Detail | C_ID |
+---------+-------------+------+
| 111     | Monitor     | 1    |
| 112     | Processor   | 2    |
| 113     | Keyboard    | 2    |
| 114     | Mouse       | 3    |
| 115     | CPU         | 5    |
+---------+-------------+------+
5 rows in set (0.00 sec)

INNER Joins

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 :

mysql> SELECT * FROM Client
    -> INNER JOIN Products
    -> ON Client.C_ID=Products.C_ID;
+------+---------------+----------+---------+-------------+------+
| C_ID | Name          | City     | Prod_ID | Prod_Detail | C_ID |
+------+---------------+----------+---------+-------------+------+
| 1    | A K Ltd       | Delhi    | 111     | Monitor     | 1    |
| 2    | V K Associate | Mumbai   | 112     | Processor   | 2    |
| 2    | V K Associate | Mumbai   | 113     | Keyboard    | 2    |
| 3    | R K India     | Banglore | 114     | Mouse       | 3    |
+------+---------------+----------+---------+-------------+------+
4 rows in set (0.04 sec)

OUTER Joins

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 :

mysql> SELECT * FROM CLIENT
    -> LEFT OUTER JOIN Products
    -> ON Client.C_ID=Products.C_ID;
+------+---------------+----------+---------+-------------+------+
| C_ID | Name          | City     | Prod_ID | Prod_Detail | C_ID |
+------+---------------+----------+---------+-------------+------+
| 1    | A K Ltd       | Delhi    | 111     | Monitor     | 1    |
| 2    | V K Associate | Mumbai   | 112     | Processor   | 2    |
| 2    | V K Associate | Mumbai   | 113     | Keyboard    | 2    |
| 3    | R K India     | Banglore | 114     | Mouse       | 3    |
| 4    | R S P Ltd     | Kolkata  | NULL    |             | NULL |
+------+---------------+----------+---------+-------------+------+
5 rows in set (0.00 sec)

In the following example we are using the ORDER BY Clause with the LEFT OUTER Join.

mysql> SELECT * FROM Client
    -> LEFT OUTER JOIN Products
    -> ON Client.C_ID=Products.C_ID
    -> ORDER BY Client.City;
+------+---------------+----------+---------+-------------+------+
| C_ID | Name          | City     | Prod_ID | Prod_Detail | C_ID |
+------+---------------+----------+---------+-------------+------+
| 3    | R K India     | Banglore | 114     | Mouse       | 3    |
| 1    | A K Ltd       | Delhi    | 111     | Monitor     | 1    |
| 4    | R S P Ltd     | Kolkata  | NULL    |             | NULL |
| 2    | V K Associate | Mumbai   | 113     | Keyboard    | 2    |
| 2    | V K Associate | Mumbai   | 112     | Processor   | 2    |
+------+---------------+----------+---------+-------------+------+
5 rows in set (0.08 sec)

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 :

mysql> SELECT * FROM Client
    -> RIGHT OUTER JOIN Products
    -> ON Client.C_ID=Products.C_ID;
+------+---------------+----------+---------+-------------+------+
| C_ID | Name          | City     | Prod_ID | Prod_Detail | C_ID |
+------+---------------+----------+---------+-------------+------+
| 1    | A K Ltd       | Delhi    | 111     | Monitor     | 1    |
| 2    | V K Associate | Mumbai   | 112     | Processor   | 2    |
| 2    | V K Associate | Mumbai   | 113     | Keyboard    | 2    |
| 3    | R K India     | Banglore | 114     | Mouse       | 3    |
| NULL |               |          | 115     | CPU         | 5    |
+------+---------------+----------+---------+-------------+------+
5 rows in set (0.03 sec)

SELF Join

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.

mysql> SELECT b.C_ID,b.Name,b.City FROM Client a, Client b
    -> WHERE a.City=b.City AND a.C_ID=1;
+------+----------+-------+
| C_ID | Name     | City  |
+------+----------+-------+
| 1    | A K Ltd  | Delhi |
| 5    | A T Ltd  | Delhi |
| 6    | D T Info | Delhi |
+------+----------+-------+
3 rows in set (0.00 sec)

we can write this SELF JOIN Query in Subquery like this also :

mysql> SELECT * FROM Client
    -> WHERE City=(
    -> SELECT City FROM Client
    -> WHERE C_ID=1);
+------+----------+-------+
| C_ID | Name     | City  |
+------+----------+-------+
| 1    | A K Ltd  | Delhi |
| 5    | A T Ltd  | Delhi |
| 6    | D T Info | Delhi |
+------+----------+-------+
3 rows in set (0.03 sec)

Joins

Joins

Joins

Tutorials