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 in SQL
FULL JOIN in SQL combines rows from both tables i.e. left and right table when there is a match in either of the table.
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.
Syntax
We can perform the FULL JOIN both with and without the WHERE clause.
SQL FULL JOIN syntax Without WHERE clause:
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
SQL FULL JOIN syntax with WHERE clause
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column
WHERE condition;
SQL FULL JOIN Examples
Let’s look at some examples of the FULL JOIN in SQL and understand it’s working.
First, let’s create a demo database and two tables on which we will perform the JOIN.
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:
CREATE DATABASE w3wiki;
USE w3wiki;
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 with WHERE Clause Example
In this example, 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
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
Important Points About 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.