use Perl

• Cross JOIN (Score:1)

  by cosimo (4138)

  Cool!
  
  I think I must review what the "CROSS JOIN" does in my SQL manual... :-)
  Thanks for sharing.
  • Re:Cross JOIN (Score:1)

    by Aristotle (5147) <pagaltzis@gmx.de> on 2007.03.29 1:39 (#54034) Homepage Journal

    CROSS JOIN is simply an explicit way to request the standard cartesian product.

    sqlite> CREATE TABLE tbl (num INTEGER);
    sqlite> INSERT INTO tbl VALUES (  1);
    sqlite> INSERT INTO tbl VALUES (  2);
    sqlite> INSERT INTO tbl VALUES (  3);
    sqlite> SELECT t1.num A, t2.num B FROM tbl t1 CROSS JOIN tbl t2;
    A           B
    ----------  ----------
    1           1
    1           2
    1           3
    2           1
    2           2
    2           3
    3           1
    3           2
    3           3

    You can write the exact same query like this:

    SELECT t1.num A, t2.num B FROM tbl t1, tbl t2

    I just prefer the newer syntax because there are few cases where you’d deliberately ask for a cartesian product, and with the old syntax it’s not clear whether you did so or merely forgot the join condition. The new explicit syntax documents your intent.

    Reply to This

    Parent