SQL FULL JOIN
Last Updated :
21 Mar, 2024
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.
Share your thoughts in the comments
Please Login to comment...