SQL Full Join – All you need to get started!

Filed Under: SQL
SQL FULL JOIN

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.

FULL JOIN
FULL JOIN

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!

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.

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);
Table-1 Stud_Info
Table-1 Stud_Info
Table-2 Stud_score
Table-2 Stud_score

Now, let us apply the concept of Full Join on the above tables as shown–

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:

SQL FULL JOIN

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!!


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages