Prev Next
- SQL FULL JOIN is used to combine the two tables together. FULL JOIN selects and returns all records from both left and right table and displays in the output. All records are returned even if there is no single matching record between both tables.
- And, a NULL value is returned for all columns if no matching records found in respective records.
- FULL JOIN is also called as FULL OUTER JOIN.
SQL Syntax for Full Join (Full outer join):
| SQL Syntax for FULL JOIN (FULL OUTER JOIN) | SELECT table1.column1, table2.column2, etc FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name; |
| Or Another Equivalent SQL Syntax for FULL JOIN (FULL OUTER JOIN) |
SELECT table1.column1, table2.column2, etc FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name; |
Please consider following 2 tables with few records as given below.
Table1:
Table name (for example): student1
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows
Table2:
Table name (for example): student2
Column names in this table: Student_ID, Department, College and Rank
Available records: 4 rows
Example: how to use SQL Full Join (Full outer join):
SQL Query:
SELECT student1. Student_name, student1.City, student2. Department, student2.Rank
FROM student1
FULL JOIN student2
ON student1.Student_ID = student2.Student_ID;
Or
SELECT student1. Student_name, student1.City, student2. Department, student2.Rank
FROM student1
FULL OUTER JOIN student2
ON student1.Student_ID = student2.Student_ID;
Description:
In above SQL FULL JOIN, all records from both left table & right table are returned and displayed even if there is no single matching record between them. And, NULL values are returned for all un-matching columns.
SQL query Output:
Note:
FULL JOIN operation is also as equal as UNION ALL operation which is used to join 2 tables together with all records irrespective of matching records between them.
CONTINUE ON OTHER SQL JOINS:
Click on each SQL joins below for detailed description and example SQL queries.
| Type of SQL JOINS | Description |
| SQL – Inner Join (Simple Join) | It is used to combine two or more tables together using the matching columns from both tables. |
| SQL – Left Join (Left outer join) | LEFT JOIN selects all records from left table and also selects all matching records from the right table. |
| SQL – Right Join (Right outer join) | RIGHT JOIN selects all records from right table and also selects all matching records from the left table. |
| SQL – Full Join (Full outer join) | FULL JOIN selects and returns all records from both left and right tables. |
| SQL – Self Join | Self Join is used to join a table to itself. |
| SQL – Cartesian Join or Cross Join | Cartesian Join returns the Cartesian products of 2 or more tables joined together. |