SQL JOIN ON/WHERE Operators

Join tables with other operators in the WHERE or ON keywords.

  • To link data from multiple tables together, you can use other operators than just equality (=) between the columns in the tables.These operators are:
    Symbol Meaning
    > greater than
    >= greater than or equal to
    < less than
    <= less than or equal to
    <> not equal to
    This Example 1 uses JOIN and ON with operator < (less than) to connect together data from two tables:
    SQL server, MySQL, Oracle, PostgreSQL :
    Select   a.US_STATE_CD , ADDR_LINE "Address", 
       CITY , u.US_STATE_CD
     from ADDRESSES a  JOIN US_STATES u
     ON a.US_STATE_CD < u.US_STATE_CD
     and u.US_STATE_CD LIKE 'C%'
     order by a.US_STATE_CD, u.US_STATE_CD
    The result should be:
    us_state_cd Address city us_state_cd
    AR 3009 Table Rock Road Biglerville Alleene CA
    AR 3009 Table Rock Road Biglerville Alleene CA
    AR 3009 Table Rock Road Biglerville Alleene CO
    AR 3009 Table Rock Road Biglerville Alleene CO
    AR 3009 Table Rock Road Biglerville Alleene CT
    AR 3009 Table Rock Road Biglerville Alleene CT
    CA 3871 San Antonio Drive Indio Agoura Hills CO
    CA 5325 N Sheridan Road Aguanga CO
    CA 5325 N Sheridan Road Aguanga CT
    CA 3871 San Antonio Drive Indio Agoura Hills CT
    This Example 2 uses WHERE with operator > (greater than) to connect together data from two tables:
    SQL server, MySQL, Oracle, PostgreSQL :
    Select   a.US_STATE_CD , ADDR_LINE "Address", 
       CITY , u.US_STATE_CD
     from ADDRESSES a , US_STATES u
     WHERE a.US_STATE_CD > u.US_STATE_CD
     and u.US_STATE_CD LIKE 'C%'
     order by a.US_STATE_CD, u.US_STATE_CD
    The result should be:
    us_state_cd Address city us_state_cd
    FL 4415 Metro Parkway Fort Myers Calford CA
    FL 9468 Wandering Way Columbia Alachua CA
    FL 4415 Metro Parkway Fort Myers Calford CO
    FL 9468 Wandering Way Columbia Alachua CO
    FL 4415 Metro Parkway Fort Myers Calford CT
    FL 9468 Wandering Way Columbia Alachua CT
    MA 1900 Manakin Road Manakin Sabot Abington CA
    MA 1900 Manakin Road Manakin Sabot Abington CA
    MA 1900 Manakin Road Manakin Sabot Abington CO
    MA 1900 Manakin Road Manakin Sabot Abington CO
    MA 1900 Manakin Road Manakin Sabot Abington CT
    MA 1900 Manakin Road Manakin Sabot Abington CT
    NJ 195 Cedar Avenue Staten Island Bordentown CA
    NJ 195 Cedar Avenue Staten Island Bordentown CO
    NJ 195 Cedar Avenue Staten Island Bordentown CT
    NY 2581 Richmond Terrace Staten Island Mappleton CA
    NY 2581 Richmond Terrace Staten Island Mappleton CO
    NY 2581 Richmond Terrace Staten Island Mappleton CT
© 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.