Copyright © tutorialspoint.com

# SQL - FULL JOINS

The SQL FULL JOIN combines the results of both left and right outer joins.

The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

# Syntax:

The basic syntax of FULL JOIN is as follows:

    SELECT table1.column1, table2.column2...
    FROM table1
    FULL JOIN table2
    ON table1.common_filed = table2.common_field;

Here given condition could be any given expression based on your requirement.

# Example:

Consider following two tables, (a) CUSTOMERS table is as follows:

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
    |  2 | Khilan   |  25 | Delhi     |  1500.00 |
    |  3 | kaushik  |  23 | Kota      |  2000.00 |
    |  4 | Chaitali |  25 | Mumbai    |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal    |  8500.00 |
    |  6 | Komal    |  22 | MP        |  4500.00 |
    |  7 | Muffy    |  24 | Indore    | 10000.00 |
    +----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

    +-----+---------------------+-------------+--------+
    | OID | DATE                |          ID | AMOUNT |
    +-----+---------------------+-------------+--------+
    | 102 | 2009-10-08 00:00:00 |           3 |   3000 |
    | 100 | 2009-10-08 00:00:00 |           3 |   1500 |
    | 101 | 2009-11-20 00:00:00 |           2 |   1560 |
    | 103 | 2008-05-20 00:00:00 |           4 |   2060 |
    +-----+---------------------+-------------+--------+

Now let us join these two tables using FULL JOIN as follows:

    SQL> SELECT  ID, NAME, AMOUNT, DATE
         FROM CUSTOMERS
         FULL JOIN ORDERS
         ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce following result:

    +------+---------+--------+---------------------+
    | ID   | NAME    | AMOUNT | DATE                |
    +------+---------+--------+---------------------+
    |    3 | kaushik |   3000 | 2009-10-08 00:00:00 |
    |    3 | kaushik |   1500 | 2009-10-08 00:00:00 |
    |    2 | Ramesh  |   1560 | 2009-11-20 00:00:00 |
    |    4 | kaushik |   2060 | 2008-05-20 00:00:00 |
    |    1 | Ramesh  |   NULL | NULL                |
    |    2 | Ramesh  |   1560 | 2009-11-20 00:00:00 |
    |    3 | kaushik |   3000 | 2009-10-08 00:00:00 |
    |    3 | kaushik |   1500 | 2009-10-08 00:00:00 |
    |    4 | kaushik |   2060 | 2008-05-20 00:00:00 |
    |    5 | Hardik  |   NULL | NULL                |
    |    6 | Komal   |   NULL | NULL                |
    |    7 | Muffy   |   NULL | NULL                |
    +------+---------+--------+---------------------+

If your Database does not support FULL JOIN then you can use UNION ALL clause to combile two JOINS as follows:

    SQL> SELECT  ID, NAME, AMOUNT, DATE
         FROM CUSTOMERS
         LEFT JOIN ORDERS
         ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
    UNION ALL
         SELECT  ID, NAME, AMOUNT, DATE
         FROM CUSTOMERS
         RIGHT JOIN ORDERS
         ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

Copyright © tutorialspoint.com