Hey, folks! In this article, we will be focusing on SQL Full Join in detail.
As seen in our previous posts, SQL Joins enable us to associate the data values from different columns together on the basis of a predefined condition.
Today, we will be having a look at Full Join.
So, let us begin!!
Understanding SQL Full Join
Full Join enables the database administrator to combine the data records of a couple of tables altogether in accordance to a specified condition.
In a Full Join, all the matching records are returned from both the tables i.e. the right and left tables. Thus, Full Join is considered as a combination of the properties of Left Join and Right Join, respectively.
Having understood the working of Full Join, let us now focus on the structure in the upcoming section.
Syntax of Full Join in SQL
Have a look at the below syntax!
|
1 2 3 4 |
SELECT columns FROM table_1 FULL JOIN table_2 ON table_1.column-name = table_2.column-name; |
From the above command, the following values are returned–
- All the matching data records from left as well as right table of the database.
- If any record of the either table(left or right) does not matches the other records, it displays a NULL value.
Thus, it is a summation of the Left Join and Right Join.
Implementing Full Join in SQL
NOTE: MYSQL does not support full join. Instead, the SQL UNION ALL clause is used to perform the operation of Full join as shown in the below example.
Initially, we have created two tables ‘Stud_Info’ and “Stud_score’ using SQL Create and Insert query to display the data of a group of students.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
create table Stud_Info(stud_id integer, Name varchar(200), sub varchar(200)); insert into Stud_Info(stud_id, Name, sub) values(001, 'John','Maths'); insert into Stud_Info(stud_id, Name, sub) values(002, 'Mary','Science'); insert into Stud_Info(stud_id, Name, sub) values(003, 'Ray','Social Science'); insert into Stud_Info(stud_id, Name, sub) values(004, 'Jim','History'); insert into Stud_Info(stud_id, Name, sub) values(005, 'Rick','Geo'); create table Stud_score(stud_id integer, Score Integer); insert into Stud_score(stud_id, score) values(001, 25); insert into Stud_score(stud_id, score) values(002, 45); insert into Stud_score(stud_id, score) values(005, 46); insert into Stud_score(stud_id, score) values(004, 68); insert into Stud_score(stud_id, score) values(007, 100); |
Now, let us apply the concept of Full Join on the above tables as shown–
|
1 2 3 4 5 6 7 8 |
SELECT Stud_Info.stud_id, Stud_Info.Name, Stud_score.score FROM Stud_Info LEFT JOIN Stud_score ON Stud_Info.stud_id = Stud_score.stud_id UNION ALL SELECT Stud_Info.stud_id, Stud_Info.Name, Stud_score.score FROM Stud_Info RIGHT JOIN Stud_score ON Stud_Info.stud_id = Stud_score.stud_id |
Output:
Conclusion
By this, we have come to the end of this topic. Feel free to comment below, in case you come across any question.
Till then, Happy Learning!!