What is Joins in SQL?
Join is an technique in SQL to fetch the data from multiple tables. whenever our requirement is to fetch the data from multiple table then you should used joins.
There are mainly 4 types of joins in SQL.
- Inner Join
- Left Outer Join
- Right Outer Join
- Full Join
Its dependents upon your requirement that which type of join you can use. All are having it's own importance let's see one by one with examples
1) Inner Join
When you want to fetch data from multiple table with only matching records from both that time you should use Inner Join. It will return only those records for which condition matched.
Syntax of Inner Join
SELECT t1.column_name1, t2.column_name1 FROM table_name1 t1
INNER JOIN table_name2 t2
ON t1.column_name2 = t2.column_name2;
Example of Inner Join
SELECT s.student_name, s.student_roll_no, m.marks FROM student s
INNER JOIN marks m
ON s.student_roll_no = m.student_roll_no;
There student_roll_no is the primary key column from student table and the student_roll_no form marks table is the foreign key.
2) Left Outer Join
The Left Outer join returns all records from the left table i.e. table1, and the matched records from the right table i.e. table2. The result is NULL from the right side table, if there is no match.
Syntax of Left Outer Join
SELECT t1.column_name1, t2.column_name1 FROM table_name1 t1
LEFT OUTER JOIN table_name2 t2
ON t1.column_name2 = t2.column_name2;
Example of Left Outer Join
SELECT s.student_name, s.student_roll_no, m.marks FROM student s
LEFT OUTER JOIN marks m
ON s.student_roll_no = m.student_roll_no;
3) Right Outer Join
The Right Outer join returns all records from the right table i.e. table2, and the matched records from the left table i.e. table1. The result is NULL from the left side table, if there is no match.
Syntax of Right Outer Join
SELECT t1.column_name1, t2.column_name1 FROM table_name1 t1
RIGHT OUTER JOIN table_name2 t2
ON t1.column_name2 = t2.column_name2;
Example of Right Outer Join
SELECT s.student_name, s.student_roll_no, m.marks FROM student s
RIGHT OUTER JOIN marks m
ON s.student_roll_no = m.student_roll_no;
4) Full Outer Join
The Full Outer Join returns all records when there is a match in between left (table1) or right (table2) table or there is no match in between left or right table. That means it returns complete records from both the table.
The Full Outer join can potentially return very large result-sets.
The Full Outer join is same as Full join. You can use either of them.
Syntax of Full Outer Join
SELECT t1.column_name1, t2.column_name1 FROM table_name1 t1
FULL OUTER JOIN table_name2 t2
ON t1.column_name2 = t2.column_name2;
Example of Full Outer Join
SELECT s.student_name, s.student_roll_no, m.marks FROM student s
FULL OUTER JOIN marks m
ON s.student_roll_no = m.student_roll_no;
