SQL CROSS JOIN

SQL using CROSS JOIN.

  • CROSS JOIN - Gives all the possible combinations of the rows from each of the tables joined.
  • When using CROSS JOIN, the ON and USING keywords should not be allowed, even for some system you can.
  • A skeleton syntax version of CROSS JOIN is this:
    SELECT select_list
    FROM table_1 
    CROSS JOIN table_2
    [WHERE search_conditions ]
    [ORDER BY column1, column2, ... ]
    Example using CROSS JOIN between the tables:
    SQL server, MySQL, Oracle, PostgreSQL :
    Select First_name "First name",
      Last_name "Last name",
      email, Telephone_type "Phone type",
      Telephone_number as "Phone number"
     from customers 
     CROSS JOIN telephones 
     WHERE Telephone_type='Home'
     order by Last_name;
    The result should be:
    First name Last name email Phone type Phone number
    Judith Bowman J.Bowman@imb.com Home (679) 234-94
    Judith Bowman J.Bowman@imb.com Home (978) 667-94
    Marcy Darnovsky Marcy@oracle.com Home (978) 667-94
    Marcy Darnovsky Marcy@oracle.com Home (679) 234-94
    Sandra Emerson Sandra.Emerson@mySql.com Home (679) 234-94
    Sandra Emerson Sandra.Emerson@mySql.com Home (978) 667-94
    Ricard Nixon Ricard.Nixon@hotmail.com Home (679) 234-94
    Ricard Nixon Ricard.Nixon@hotmail.com Home (978) 667-94
    Robert Redford R.Redford@google.com Home (679) 234-94
    Robert Redford R.Redford@google.com Home (978) 667-94
© 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.