SQL FULL JOIN

SQL using FULL JOIN.

  • FULL JOIN - Return rows when there is a match in one of the tables.

    Pictorial representation :

  • FULL JOIN and FULL OUTER JOIN are the same.
  • Not every RDBMS has implemented FULL JOIN.
  • A skeleton version of FULL JOIN syntax is this:
    SELECT select_list
    FROM table_1 FULL [OUTER] JOIN table_2
    ON [table_1.]column  join_operator  [table_2.]column
    [WHERE search_conditions ]
    [ORDER BY column1, column2, ... ]
    Example using FULL JOIN between the tables:
    MySQL does not support FULL JOIN.
    SQL server, Oracle, PostgreSQL :
    Select a.US_STATE_CD "Addresses code", ADDR_LINE "Address",
            CITY, u.US_STATE_CD "State code", US_STATE_NM 
     from US_STATES u
     FULL OUTER JOIN ADDRESSES a
     ON a.US_STATE_CD=u.US_STATE_CD
    WHERE US_STATE_NM LIKE 'A%'
    ORDER BY u.US_STATE_CD;
    The result should be:
    Addresses code Address city State code us_state_nm
    AK Alaska
    AL Alabama
    AR 3009 Table Rock Road Biglerville Alleene AR Arkansas
    AR 3009 Table Rock Road Biglerville Alleene AR Arkansas
    AZ Arizona
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.