Left & Right Join In Mysql

A join is used to retrieve needed data based on the relationships that are shared between them.

Creating Left Join

A left join returns the rows from the left hand side (primary table, as I like to call it) table, and the corresponding matches from the right hand side table (the joined table), and has NULL values if there were no matches in the joined table. In case the primary table has multiple rows which matches with a single row in the joined table, then for each matching row of the primary table the row from the joined table is repeated.

Basic Form Of Left Join

Code:

left_table LEFT JOIN right_table join_condition

Table that appears left side of the LEFT JOIN is refers to as "left table". Nothing tricky about that.

The ON condition is used to decide how to retrieve rows from table appears on the right side of join.

If there is a row in left table that matches the WHERE clause, but there is no row in right table that matches the ON condition, an extra right_table row is generated with all columns set to NULL.

Only after the ON condition WHERE clause will be used. It will filter out rows retrieved from the ON condition.

Practical Example

For the example, let's create a table and add some data to it.

Code:

CREATE TABLE `Employee` (
     `id`            int,
     `first_name`    VARCHAR(15),
     `last_name`     VARCHAR(15),
     `desig_id`     int,
     `DOJ`          DATE
         );

insert into `Employee` values (1,'Ramesh','Prasad',1,'19960917');
insert into `Employee` values (2,'Dilip','Das',2,'19870730');
insert into `Employee` values (3,'Prakash','Chandra',3,'19821024');
insert into `Employee` values (4,'Sourav','Roy',4,'19960725');
insert into `Employee` values (5,'Vijay','Kumar',5,'19821018');
insert into `Employee` values (6,'Arun','Kumar',0,'19821011');

Now, let's check if everthing's in place.

Code:

mysql> select * from employee;
+------+------------+-----------+---------------+-------------+
| id   | first_name | last_name | desig_id      | DOJ          |
+------+------------+-----------+---------------+-------------+
|    1 | Ramesh     | Prasad    | 1            | 1996-09-17  |
|    2 | Dilip      | Das       | 2            | 1987-07-30  |
|    3 | Prakash    | Chandra   | 3        | 1982-10-24  |
|    4 | Sourav     | Roy       | 4        | 1996-07-25  |
|    5 | Vijay      | Kumar     | 5        | 1982-10-18  |
|    6 | Arun       | Kumar     | 0        | 1982-10-11  |
+------+------------+-----------+-----------------------------+

We would need another tale for joining, let's now create it & fill it up with some dummy data.

Code:

create table `designation` (
        `id`         int,
        `title`      VARCHAR(20)
 );

insert into `designation` values (1,'Operations');
insert into `designation` values (2,'Executive');
insert into `designation` values (3,'Accountant');
insert into `designation` values (4,'Programmer');
insert into `designation` values (5,'Content');

Check the new table.

Code:

mysql> select * from designation;
+------+------------+
| id   | title      |
+------+------------+
|    1 | Operations |
|    2 | Executive  |
|    3 | Accountant |
|    4 | Programmer |
|    5 | Content    |
+------+------------+

Now, the SQL for left join, here we would join employee against designation to get the designation names for the employees in employee table, and the example will demonstrate that the unmatched row from employee has a NULL field.

Code:

mysql> SELECT employee.first_name, employee.last_name, designation.title
    -> FROM employee
    -> LEFT JOIN designation
    -> ON employee.desig_id = designation.ID;
+------------+-----------+------------+
| first_name | last_name | title      |
+------------+-----------+------------+
| Ramesh     | Prasad    | Operations |
| Dilip      | Das       | Executive  |
| Prakash    | Chandra   | Accountant |
| Sourav     | Roy       | Programmer |
| Vijay      | Kumar     | Content    |
| Arun       | Kumar     | NULL       |
+------------+-----------+------------+

Now, let's try using a WHERE clause to filter out rows retrieved from the ON condition.

Code:

mysql> SELECT employee.first_name, employee.last_name, designation.title
    -> FROM employee
    -> LEFT JOIN designation
    -> ON employee.desig_id = designation.ID
    -> WHERE designation.title = 'Executive';

+------------+-----------+-----------+
| first_name | last_name | title     |
+------------+-----------+-----------+
| Dilip      | Das       | Executive |
+------------+-----------+-----------+

In a variation of the left join, we can use the USING clause, see the code example below to know how.

Code:

mysql> SELECT employee.first_name, designation.title
    ->     FROM employee
    ->     LEFT JOIN designation
    ->     USING (ID);
+------------+------------+
| first_name | title      |
+------------+------------+
| Ramesh     | Operations |
| Dilip      | Executive  |
| Prakash    | Accountant |
| Sourav     | Programmer |
| Vijay      | Content    |
| Arun       | NULL       |
+------------+------------+

Creating Right Join

A Right Join is a alteration of Left Join here all the data on the RIGHT side of the join (the second table) is returned even if there is no matching data found in left table(the first table).

Code:

mysql> SELECT employee.first_name, employee.last_name, designation.title
    -> FROM employee
    -> RIGHT JOIN designation
    -> ON desig_id.ID = designation.ID;
+------------+-----------+------------+
| first_name | last_name | title      |
+------------+-----------+------------+
| Ramesh     | Prasad    | Operations |
| Dilip      | Das       | Executive  |
| Prakash    | Chandra   | Accountant |
| Sourav     | Roy       | Programmer |
| Vijay      | Kumar     | Content    |
+------------+-----------+------------+

Note: Joins are not the only method you can use to access multiple tables in a single statement. MySQL also supports the use of sub-queries. A sub-query, also referred to as a sub-select.

 

hi hi thanks Sir so much