SQL FULL JOIN

SQL FULL JOIN or FULL OUTER JOIN returns a new table containing all records of the left and right table on a match. FULL JOIN can be considered as the combination of LEFT JOIN and RIGHT JOIN.

When there is no match at a given position then NULL is displayed at that particular position.

Tip: We can use FULL JOIN to combine multiple tables, by sequentially performing FULL JOIN on two tables at a time.

SQL FULL JOIN Syntax

We can perform the FULL JOIN both with and without the WHERE clause.

Without WHERE clause

SELECT columns

FROM table1

FULL OUTER JOIN table2

ON table1.column = table2.column;

With WHERE clause

SELECT columns

FROM table1

FULL OUTER JOIN table2

ON table1.column = table2.column

WHERE condition;

Demo SQL Database

For this FULL JOIN tutorial, we will use the following tables in examples

Table 1- Students

ID	NAME	BRANCH	NUMBER
1	SURYANSH JOHARI	CS	984012
2	AMAN SHARMA	IT	771346
3	DEV VERMA	ME	638587
4	JOY SMITH	CE	876691
5	CHARLES GATTO	EE	997679

Table 2- Library

BOOK_ID	BOOK_NAME	ISSUED_ON	DUE_DATE
1	RD SHARMA	2023-01-01	2023-01-08
2	GATE CRACKER	2023-02-02	2023-02-09
3	MORRIS MANO	2023-03-03	2023-03-10
4	NK PUBLICATIONS	2023-04-04	2023-04-11
5	BIG BANG THEORY	2023-05-05	2023-05-12

To create these tables, in your system write the following queries:

MySQL

CREATE DATABASE GeeksForGeeks;
USE GeeksForGeeks;

CREATE TABLE STUDENTS(
ID INT,
NAME VARCHAR(20),
BRANCH VARCHAR(20),
NUMBER INT);

CREATE TABLE LIBRARY(
BOOK_ID INT,
BOOK_NAME VARCHAR(20),
ISSUED_ON DATE,
DUE_DATE DATE);
  
INSERT INTO STUDENTS VALUES(1,'SURYANSH JOHARI','CS',984012);
INSERT INTO STUDENTS VALUES(2,'AMAN SHARMA','IT',771346);
INSERT INTO STUDENTS VALUES(3,'DEV VERMA','ME',638587);
INSERT INTO STUDENTS VALUES(4,'JOY SMITH','CE',876691);
INSERT INTO STUDENTS VALUES(5,'CHARLES GATTO','EE',997679);
  
INSERT INTO LIBRARY VALUES(1,'RD SHARMA','2023-01-01','2023-01-08');
INSERT INTO LIBRARY VALUES(2,'GATE CRACKER','2023-02-02','2023-02-09');
INSERT INTO LIBRARY VALUES(3,'MORRIS MANO','2023-03-03','2023-03-10');
INSERT INTO LIBRARY VALUES(4,'NK PUBLICATIONS','2023-04-04','2023-04-11');
INSERT INTO LIBRARY VALUES(5,'BIG BANG THEORY','2023-05-05','2023-05-12');

SQL FULL JOIN Examples

Let’s look at some examples of FULL OUTER JOIN in SQL, to understand how it works in different use cases.

Example 1

We are doing FULL JOIN without the WHERE clause and finding the records from both the tables joining with STUDENTS.ID and BOOK_ID.

Query

 SELECT ID,NAME,BOOK_ID,BOOK_NAME
 FROM STUDENTS
 FULL JOIN LIBRARY
 ON ID=BOOK_ID;

Output

Performing Full Join

Example 2

We are doing FULL JOIN with the WHERE clause and finding the records from both the tables joining with STUDENTS.ID and BOOK_ID.

Query

 SELECT ID,NAME,BOOK_ID,BOOK_NAME
      FROM STUDENTS
      FULL JOIN LIBRARY
      ON ID=BOOK_ID
      WHERE BRANCH='CS';

Output

Full Join with where clause

Key Takeaways from SQL FULL JOIN

• FULL JOIN or a FULL OUTER JOIN is a type of Outer JOIN that combines records from both the left table and the right table. 
• When there is no match at a given position then NULL is being displayed at that particular position. 
• A Full Join is a combination of both the Left Outer Join and the Right Outer Join.
• You can join multiple tables, by performing FULL JOIN on two tables and combining the resulting table with another table.

Frequently Asked Questions About SQL Full Join

What is a Full Join in SQL?

Full Join is a type of outer join which combines each records of both the left and the right table.

Is Full Join and Full Outer Join the same?

Yes, a Full Join and Full Outer Join are the same.

What happens when a record from the Left Table does not match with the Right Table?

When a record from Left Table does not match with Right Table then at that particular position NULL is being displayed.