FULL

MySQL

Example

SELECT
    CONCAT_WS(' ', FirstName, LastName) AS FullName
FROM
    Employees;

Output

+-------------------+
| FullName          |
+-------------------+
| John Doe          |
| Jane Smith        |
| Mary Johnson      |
| James Brown       |
+-------------------+

Explanation

In the code given, the CONCAT_WS function was used to concatenate the FirstName and LastName columns from the Employees table with a space separating them. The result was stored in a new column called FullName. The output included the table showing the full names of employees.

SQL Server

Example

SELECT *
FROM Employee
FULL OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

Output

| EmployeeID | Name  | DepartmentID | DepartmentID |   DepartmentName |
|------------|-------|--------------|--------------|------------------|
|          1 |   Amy |            1 |            1 |          Finance |
|          2 |  Brad |            2 |            2 |       Technology |
|          3 | Clara |         NULL |         NULL |             NULL |
|       NULL |  NULL |         NULL |            3 | Human Resources  |

Explanation

The FULL OUTER JOIN keyword returns all records when there is a match in either left (Employee) or right (Department) table records. In instances where there is no match, the result is NULL on either side. In this example, ‘Clara’ does not belong to any department and ‘Human Resources’ does not have any employee.

Oracle

Example

SELECT *
FROM Employees
FULL JOIN Departments
ON Employees.Department_ID = Departments.Department_ID;

Output

| Employee_ID | Employee_Name | Department_ID | Department_Name   |
|-------------|---------------|---------------|-------------------|
| 1           | John Doe      | 10            | Sales             |
| 2           | Jane Smith    | 20            | Human Resources   |
| 3           | Mary Johnson  | null          | null              |
| null        | null          | 30            | Engineering       |

Explanation

The FULL OUTER JOIN keyword in Oracle is used to combine the results of both left and right outer joins. The resultant table will include records from both the tables. If there is no match, the result is NULL on either side.