Scribd
Upload
61 views2 pages

MySQL Join Operations Explained

A JOIN clause combines rows from two or more tables based on related columns. There are two types of joins: inner join and outer join. Outer join has three types - left, right, and full outer join. Left outer join returns all records from the left table and matched records from the right table, returning null for unmatched right table records. Right outer join returns all records from the right table and matched left table records, returning null for unmatched left records. Full outer join is the union of left and right outer joins, returning all records from both tables and null for unmatched records. Inner join returns only the matching records from both tables.

Uploaded by

om
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
61 views2 pages

MySQL Join Operations Explained

A JOIN clause combines rows from two or more tables based on related columns. There are two types of joins: inner join and outer join. Outer join has three types - left, right, and full outer join. Left outer join returns all records from the left table and matched records from the right table, returning null for unmatched right table records. Right outer join returns all records from the right table and matched left table records, returning null for unmatched left records. Full outer join is the union of left and right outer joins, returning all records from both tables and null for unmatched records. Inner join returns only the matching records from both tables.

Uploaded by

om
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

MySQL Join operation

A JOIN clause is used to combine rows from two or more tables, based on a related column between
them

Join is of two types

(1) outer join 2) inner join

outer join is of three types

a) Left outer join b) right outer join c) full outer join

select* from student;

rollno Sname class


1 Rajeev Xii
2 Saksham Xii
3 jay xii

3 rows in set (0.00 sec)

mysql> select* from marks;

rollno sub marks


2 Ip 67
3 Ip 87
4 ip 83

3 rows in set (0.00 sec)

a) Left outer join:-


Returns all the records from left table and matched records from right table. Returns Null for
unmatched records of the right table.

mysql> select* from student left outer join marks on student.rollno=marks.rollno;

rollno sname class rollno sub marks


1 Rajeev Xii null null null
2 Saksham Xii 2 ip 67
3 jay xii 3 ip 87
3 rows in set (0.00 sec)

b) Right outer join:-


Returns all the records from right table and matched records from left table. Returns Null for
unmatched records of the left table.

mysql> select* from student right outer join marks on student.rollno=marks.rollno;

rollno sname class rollno sub marks


2 Saksham xii 2 Ip 67
3 Jay xii 3 Ip 87
null null null 4 ip 83

3 rows in set (0.00 sec)

c) Full outer join:-

It is the union of the left outer join and right outer join. It returns all the records from the both
tables. Returns null for unmatched records.

mysql> select* from student left outer join marks on student.rollno=marks.rollno

-> union

-> select* from student right outer join marks on student.rollno=marks.rollno;

Rollno Sname Class Rollno sub marks


1 Rajeev Xii null null null
2 Saksham Xii 2 Ip 67
3 Jay xii 3 Ip 87
null null null 4 ip 83

4 rows in set (0.00 sec)

2) Inner join:-

Returns the matching records from both the tables.

mysql> select* from student inner join marks on student.rollno=marks.rollno;

rollno aname class rollno sub marks


2 Saksham Xii 2 Ip 67
3 jay xii 3 ip 87

2 rows in set (0.00 sec)

You might also like