Alphalearningschool
        

<<< Prev

Up
SQL JOIN

Sql join is most important topic in sql , for using this join we can join two or more table .

Types of Joins:

  1. Outer join
  2. Left join
  3. Right join
  4. Full Join
  5. Cross join

Why join is useful.

If you want to assess the data from more than one table that time we go for join.

Query: select column_name from table_name t1 , table_name t2 where t1.column_name =t2.table_name;

Outer Join:

Outer join has two type 1) Left outer Join, 2) Right Outer Join.

 Left Outer Join:- Left outer join also known as left join.

In this left join return all row according to the left table. If left join will not get matching then right table it will give null value.

Ex: Suppose you have 5 row in left table and 10 row in right table so in result you will get 5 row with matching and rest of 5 row you will get null value. 

Right Outer Join:- Right outer join also known as Right join.

In this Right join return, all row according to the Right table. If Right join will not get matching then left table it will give null value.

Ex: Suppose you have 5 row in right table and 10 row in left table so in result you will get 5 row with matching and rest of 5 row you will get null value.

Note** There is no such a left or right table it deepens upon you which table name write first it is consider as left table and second one is right table.

Left Join:

Let’s see real world example of left join table.

Query : select table1.column_name , table2.column_name from table1 LEFT JOIN table2 ON table1.column_name =table2.column_name;

Actual Query:

SELECT `id`, `name`, `amount`,DATE 

FROM `demo_table` 

LEFT JOIN `part_demo` 

ON demo_table.ID = `part_demo`.`demo_table_id`;

Example:

mysql> select * from student;

 

+---------+--------+---------+------------+------+

| Roll_no | Name   | address | phone      | age  |

+---------+--------+---------+------------+------+

|       1 | vidhya | d       | 2147483647 |   19 |

|       2 | kumar  | m       | 2147483647 |   24 |

|       3 | mahesh | b       | 2147483647 |   26 |

|       4 | manju  | h       | 2147483647 |   30 |

|       5 | rohit  | r       | 2147483647 |   23 |

|       6 | janani | k       | 2147483647 |   31 |

|       7 | vivek  | b       | 2147483647 |   29 |

+---------+--------+---------+------------+------+

7 rows in set (0.00 sec)

mysql> select * from studentcourse;

+-----------+---------+

| course_id | Roll_no |

+-----------+---------+

|         1 |       1 |

|         2 |       2 |

|         2 |       3 |

|         3 |       4 |

|         1 |       5 |

|         4 |       9 |

|         5 |      10 |

+-----------+---------+

7 rows in set (0.00 sec)

mysql> SELECT Student.NAME,StudentCourse.COURSE_ID

    -> FROM Student

    -> LEFT JOIN StudentCourse

    -> ON StudentCourse.ROLL_NO = Student.ROLL_NO;

+--------+-----------+

| NAME   | COURSE_ID |

+--------+-----------+

| vidhya |         1 |

| kumar  |         2 |

| mahesh |         2 |

| manju  |         3 |

| rohit  |         1 |

| janani |      NULL |

| vivek  |      NULL |

+--------+-----------+

7 rows in set (0.00 sec)

Right Join:

Let’s see real world example of left join table.

Query : select table1.column_name , table2.column_name from table1 RIGHT JOIN table2 ON table1.column_name =table2.column_name;

Actual Query:

SELECT `id`, `name`, `amount`,DATE 

FROM `demo_table` 

RIGHT JOIN `part_demo` 

ON demo_table.ID = `part_demo`.`demo_table_id`;

With respect to the above left join example:

mysql> SELECT Student.NAME,StudentCourse.COURSE_ID

    -> FROM Student

    -> RIGHT JOIN StudentCourse

    -> ON StudentCourse.ROLL_NO = Student.ROLL_NO;

+--------+-----------+

| NAME   | COURSE_ID |

+--------+-----------+

| vidhya |         1 |

| kumar  |         2 |

| mahesh |         2 |

| manju  |         3 |

| rohit  |         1 |

| NULL   |         4 |

| NULL   |         5 |

+--------+-----------+

7 rows in set (0.00 sec)

FULL OUTER JOIN:

In full outer join it will give the combine result of left table and right table.

Query: SELECT table1.column1, table2.column2...

FROM table1

FULL JOIN table2

ON table1.common_field = table2.common_field;

Let’s see real world example:

SELECT * FROM demo

OUTER JOIN part_demo ON demo.id= part_demo.demo_id;

mysql> SELECT Student.NAME,StudentCourse.COURSE_ID

    -> FROM Student

    -> FULL JOIN StudentCourse

    -> ON StudentCourse.ROLL_NO = Student.ROLL_NO; 

+--------+-----------+

| NAME   | COURSE_ID |

+--------+-----------+

| vidhya |         1 |

| kumar  |         2 |

| mahesh |         2 |

| manju  |         3 |

| rohit  |         1 |

| janani |      NULL |

| vivek  |      NULL |

| NULL   |         4 |

| NULL   |         5 |

+--------+-----------+

9 rows in set (0.00 sec)