How to Returns all rows when there is a match in either left table or right table in SQL ?
To return all rows when there is a match in either left table or right table in SQL, we can use the FULL OUTER JOIN clause.
The FULL OUTER JOIN clause returns all the rows from both the left and right tables, and where there is no match, NULL values are returned.
Here is the syntax for using FULL OUTER JOIN:
SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
For example, let's say we have two tables employees and departments with the following data:
employees table:
| id | name | department_id |
|---|---|---|
| 1 | John | 1 |
| 2 | Sarah | 2 |
| 3 | Peter | 1 |
| 4 | Rachel | 3 |
departments table:
| id | name |
|---|---|
| 1 | Marketing |
| 2 | Sales |
| 3 | Finance |
| 4 | HR |
To return all rows when there is a match in either left table or right table, we can use the following SQL query:
SELECT *
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.id;
This will return the following result:
| id | name | department_id | id | name |
|---|---|---|---|---|
| 1 | John | 1 | 1 | Marketing |
| 2 | Sarah | 2 | 2 | Sales |
| 3 | Peter | 1 | 1 | Marketing |
| 4 | Rachel | 3 | 3 | Finance |
| NULL | NULL | NULL | 4 | HR |
As you can see, all rows from both tables are returned, and where there is no match, NULL values are returned.
About the author
William Pham is the Admin and primary author of Howto-Code.com. With over 10 years of experience in programming. William Pham is fluent in several programming languages, including Python, PHP, JavaScript, Java, C++.