SQL FULL JOIN

In this section, we will help you understand the concept of the SQL FULL Join clause with a few examples.

The SQL FULL Join query is executed to display the integrated results of the left join and right join.

The SQL FULL Join query display all the data, and the NULL value is returned at the missing place of the record.

The syntax of the SQL FULL Join is as follows:

SELECT table1.column_name1, table1.columnname2, table1.column_name3, table1.columnname4, table2.column_name1, table2.column_name2, table2.column_name3, table2.column_name4 FROM table1 FULL JOIN table2;

Let’s understand the SQL FULL Join with the help of examples.

Assume the following table, which has certain data.

Table Name 1- D_Students

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_ Id
202111	Vineeta Sharma	93	88	85	85	95	92	90	5
202112	Vaibhav Lokhande	85	90	92	80	85	82	86	2
202113	Yash Dhull	90	88	94	87	85	90	89	3
202114	Sonali Patole	95	90	92	88	92	90	91	4
202115	Amit Sonar	85	83	82	86	92	84	85	1
202116	Meena Mishra	78	75	80	74	85	77	78	3
202117	Mahesh Kumbhar	75	83	75	78	80	76	82	5
202118	Sakshi Patil	80	78	74	78	80	77	83	2
202119	Sopan Bhore	70	68	75	75	80	80	80	2
202220	Prajwal Lokhande	80	85	85	75	78	80	86	4
202221	Anuja Wanare	85	88	86	82	84	85	85	4
202222	Venkatesh Iyer	90	89	87	90	92	91	90	3
202223	Anushka Sen	70	75	71	74	80	78	80	1
202224	Aakash Jain	80	75	72	74	85	80	83	2
202225	Akshay Agarwal	85	83	78	88	90	82	89	3
202226	Shwetali Bhagwat	90	83	85	88	90	80	86	1
202227	Priya Wagh	80	83	85	80	82	85	87	4
202228	Saurabh Sangale	85	83	80	90	84	84	89	5
202229	Manthan Koli	85	75	84	78	82	80	86	2
202230	Mayur Jain	80	88	87	90	92	90	88	1

Table Name 2- Department

Department_Id	Department_Name
1	Computer Engineering
2	Information Technology
3	Mechanical Engineering
4	Automobile Engineering
5	Civil Engineering
6	Electrical Engineering

Example 1: Write a query on the D_students and Department table where the second_semester percentage is 78.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM D_Students FULL JOIN Department WHERE Second_Semester = 78;  

The above SQL FULL Join query joins the D_Students table and department, and displaying the records where the second_semester percentage is 78.

The output of the above example is as follows:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_Name
202218	Sakshi Patil	80	78	74	78	80	77	83	Computer Engineering
202218	Sakshi Patil	80	78	74	78	80	77	83	Information Technology
202218	Sakshi Patil	80	78	74	78	80	77	83	Mechanical Engineering
202218	Sakshi Patil	80	78	74	78	80	77	83	Automobile Engineering
202218	Sakshi Patil	80	78	74	78	80	77	83	Civil Engineering
202218	Sakshi Patil	80	78	74	78	80	77	83	Electrical Engineering

Example 2: Write a query on the D_students and Department table where Student_Name start with the letter ‘A’.

SELECT Student_Id, Student_Name, First_Semester, Second_Semester, Third_Semester, Fourth_Semester, Fifth_Semester, Sixth_Semester, Total, Department_Name FROM Department FULL JOIN D_Students WHERE Student_Name LIKE 'A%';

The above SQL FULL Join query joins the D_Students table and department, and displaying the records where the Student_Name is start with the letter 'A'.

The output of the above example is as follows:

Student_Id	Student_Name	First_ Semester	Second_ Semester	Third_ Semester	Fourth_ Semester	Fifth_ Semester	Sixth_ Semester	Total	Department_Name
202115	Amit Sonar	85	83	82	86	92	84	85	Computer Engineering
202115	Amit Sonar	85	83	82	86	92	84	85	Information Technology
202115	Amit Sonar	85	83	82	86	92	84	85	Mechanical Engineering
202115	Amit Sonar	85	83	82	86	92	84	85	Automobile Engineering
202115	Amit Sonar	85	83	82	86	92	84	85	Civil Engineering
202115	Amit Sonar	85	83	82	86	92	84	85	Electrical Engineering
202221	Anuja Wanare	85	88	86	82	84	85	85	Computer Engineering
202221	Anuja Wanare	85	88	86	82	84	85	85	Information Technology
202221	Anuja Wanare	85	88	86	82	84	85	85	Mechanical Engineering
202221	Anuja Wanare	85	88	86	82	84	85	85	Automobile Engineering
202221	Anuja Wanare	85	88	86	82	84	85	85	Civil Engineering
202221	Anuja Wanare	85	88	86	82	84	85	85	Electrical Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Computer Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Information Technology
202223	Anushka Sen	70	75	71	74	80	78	80	Mechanical Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Automobile Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Civil Engineering
202223	Anushka Sen	70	75	71	74	80	78	80	Electrical Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Computer Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Information Technology
202224	Aakash Jain	80	75	72	74	85	80	83	Mechanical Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Automobile Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Civil Engineering
202224	Aakash Jain	80	75	72	74	85	80	83	Electrical Engineering
202225	Akshay Agarwal	85	83	78	88	90	82	89	Computer Engineering
202225	Akshay Agarwal	85	83	78	88	90	82	89	Information Technology
202225	Akshay Agarwal	85	83	78	88	90	82	89	Mechanical Engineering
202225	Akshay Agarwal	85	83	78	88	90	82	89	Automobile Engineering
202225	Akshay Agarwal	85	83	78	88	90	82	89	Civil Engineering
202225	Akshay Agarwal	85	83	78	88	90	82	89	Electrical Engineering