MYSQL FullOuter Join
MYSQL
Full outer join or full join is a technique to fetch data from all rows of both tables, including matching and mismatching records.
MySQL Full Outer join
➔ MySQL does not have a direct FULL OUTER JOIN keyword.
➔ Instead, the same result can be achieved by using a combination of LEFT JOIN and RIGHT JOIN with the UNION operator.
➔ This method ensures that all records from both tables are returned, including NULL values for columns that do not match.
➔ LEFT JOIN: Retrieves all rows from table 1 (left table) and only matching rows from table 2.
➔ RIGHT JOIN: Retrieves all rows from table 2 (right table) and only matching rows from table 1.
➔ UNION: Combines the result sets of two SELECT statements and automatically removes duplicate rows.
Problem definition:
Problem: Create a full outer join between two tables emp and dept
Create table:
Example
/* Create table */
CREATE TABLE emp (
employeeId INT PRIMARY KEY,
employeeName VARCHAR(255) NOT NULL,
departmentId INT
);
Copy the code and try it out practically in your learning environment.
Create table:
Example
/* Create table */
CREATE TABLE dept (
departmentId INT PRIMARY KEY,
departmentName VARCHAR(255) NOT NULL
);
Copy the code and try it out practically in your learning environment.
Insert value:
Example
/* Insert values */
INSERT INTO emp (employeeId, employeeName, departmentId)
VALUES (1, "John Smith", 101);
INSERT INTO emp (employeeId, employeeName, departmentId)
VALUES (2, "Mary Johnson", 102);
INSERT INTO emp (employeeId, employeeName, departmentId)
VALUES (3, "Sam Brown", 103);
Copy the code and try it out in your learning environment.
Insert value:
Example
/* Insert values */
INSERT INTO dept (departmentId, departmentName)
VALUES (101, "HR");
INSERT INTO dept (departmentId, departmentName)
VALUES (102, "Finance");
INSERT INTO dept (departmentId, departmentName)
VALUES (104, "Marketing");
Copy the code and try it out in your learning environment.
Describe table structure:
Example
/* Check table structure */
DESCRIBE emp;
Copy the code and try it out practically in your learning environment.
Table structure:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| employeeId | int(11) | NO | PRI | ||
| employeeName | varchar(255) | NO | |||
| departmentId | int | YES |
Query value:
Example
/* Query data */
SELECT * FROM emp;
Copy the code and try it out practically in your learning environment.
Table Data:
| employeeId | employeeName | departmentId |
|---|---|---|
| 1 | John Smith | 101 |
| 2 | Mary Johnson | 102 |
| 3 | Sam Brown | 103 |
FULL OUTER JOIN:
Example
/* Creating full outer join */
SELECT E.employeeId, E.employeeName, D.departmentName
FROM emp E LEFT JOIN dept D
ON E.departmentId = D.departmentId
UNION
SELECT E.employeeId, E.employeeName, D.departmentName
FROM emp E RIGHT JOIN dept D
ON E.departmentId = D.departmentId;
Copy the code and try it out practically in your learning environment.
Full Outer Join output:
| EmployeeId | EmployeeName | DepartmentId |
|---|---|---|
| 1 | John Smith | HR |
| 2 | Mary Johnson | Finance |
| 3 | Sam Brown | NULL |
| NULL | NULL | Marketing |