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