This is a detailed tutorial of SQL FULL JOIN. Learn to fetch all the records that match in the LEFT as well as in the RIGHT database tables with FULL JOIN.
Use of SQL FULL JOIN
SQL FULL JOIN is used to fetch all the records from the LEFT table as well as from the RIGHT database table that are matched on the basis of a particular common column and if the record values for particular columns are not found in either of the tables, then the records are still fetched but as NULL. It is the complete opposite of SQL INNER JOIN and the combination of SQL LEFT JOIN and SQL RIGHT JOIN.
Have a look at the example to better understand the concept of FULL JOIN.
Note. FULL JOIN and FULL OUTER JOIN are the same. It’s just that in some databases, we’ve to use the full keyword FULL OUTER JOIN instead of FULL JOIN.
FULL JOIN Syntax
SELECT table_1.col_1, table_1.col_2, table_2.col_1, table_2.col_2 ...
FULL OUTER JOIN table_2
ON table_1.column_x = table_2.column_x;
The above syntax illustrating a SQL SELECT Statement will fetch all of the records from the LEFT TABLE,
table_1 and as well as from the RIGHT TABLE,
table_2 with the matching values for the column
column_x and it fetches the values as NULL for the record column values which do not have their values specified for the record in either of the tables.
Note. FULL JOIN is not available in MySQL. But you can use LEFT and RIGHT JOINS together with UNION keyword in MySQL to get a similar result set.
Consider two database tables,
salaries. The screenshots of the table structure and some of its data are given below.
The following SQL query will fetch all the records from both of these database tables and for the matching columns which do not have values in either of the database tables
salaries, NULL values will be fetched.
SELECT employees.emp_no, employees.first_name, employees.last_name, salaries.salary
FULL OUTER JOIN salaries
ON employees.emp_no = salaries.emp_no;
I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.
Also, don’t forget to Subscribe to WTMatter!