• Home
  • SQL Full Outer Join Keyword

    In SQL, a `FULL JOIN` (or `FULL OUTER JOIN`) is a type of join operation that combines the rows from two tables, including all the matched and unmatched rows from both tables. It returns a result set that includes all the rows from both tables, and if there is no match, NULL values are returned for the columns from the non-matching table.

    The basic syntax for a `FULL JOIN` is as follows:

    sql
    SELECT column_list
    FROM table1
    FULL JOIN table2 ON join_condition;

    Here's a breakdown of the components:

    - `column_list`: The list of columns you want to select from the resulting joined table.
    - `table1`: The first table in the join operation.
    - `table2`: The second table in the join operation.
    - `join_condition`: The condition that determines how the two tables are related.


    To illustrate this further, let's consider two tables: `employees` and `departments`, with the following structures:

    employees
    -----------------------------
    | emp_id | emp_name | dept_id |
    -----------------------------
    | 1 | John | 101 |
    | 2 | Jane | 102 |
    | 3 | Alice | 103 |
    | 4 | Bob | 104 |
    -----------------------------
    
    departments
    ---------------------
    | dept_id | dept_name |
    ---------------------
    | 101 | Sales |
    | 103 | Finance |
    | 105 | IT |
    ---------------------

    Suppose you want to retrieve all employees and their corresponding department names, including those employees and departments without matches. You can use a `FULL JOIN` as follows:

    sql
    SELECT employees.emp_id, employees.emp_name, departments.dept_name
    FROM employees
    FULL JOIN departments ON employees.dept_id = departments.dept_id;

    The result of this query would be:

    ---------------------------------------------------
    | emp_id | emp_name | dept_name |
    ---------------------------------------------------
    | 1 | John | Sales |
    | 2 | Jane | NULL (No matching department)|
    | 3 | Alice | Finance |
    | 4 | Bob | NULL (No matching department)|
    | NULL | NULL | IT |
    ---------------------------------------------------

    In this example, the `FULL JOIN` returns all rows from both the `employees` and `departments` tables, including both matched and unmatched rows. If a row doesn't have a match in the other table, NULL values are displayed for the columns from the non-matching table.


    Please note that not all database systems support the `FULL JOIN` syntax. In such cases, you can achieve a similar result by combining a `LEFT JOIN` and a `RIGHT JOIN` using the `UNION` or `UNION ALL` operator.



    About the Author



    Silan Software is one of the India's leading provider of offline & online training for Java, Python, AI (Machine Learning, Deep Learning), Data Science, Software Development & many more emerging Technologies.

    We provide Academic Training || Industrial Training || Corporate Training || Internship || Java || Python || AI using Python || Data Science etc





     PreviousNext