Cross Joins


You saw in the previous lesson that if no relationship between joined tables is given in the WHERE clause, a Cartesian product or cross join is produced.

The ON keyword is not required to appear after the JOIN keyword, so the same result can be obtained using INNER JOIN and no ON clause.

However, for readability, you can use the CROSS JOIN keyword to show in your query that the Cartesian product is the desired result. The following example reproduces the cross join between sample tables t1 and t2 from the previous lesson:

 mysql>  SELECT *     ->  FROM t1     ->  CROSS JOIN t2; +----+--------+----+--------+ | id | letter | id | letter | +----+--------+----+--------+ |  1 | A      |  1 | X      | |  2 | B      |  1 | X      | |  3 | C      |  1 | X      | |  1 | A      |  2 | Y      | |  2 | B      |  2 | Y      | |  3 | C      |  2 | Y      | |  1 | A      |  3 | Z      | |  2 | B      |  3 | Z      | |  3 | C      |  3 | Z      | +----+--------+----+--------+ 9 rows in set (0.01 sec) 


You will use a cross join very rarely, so it is helpful to use this syntax to make it clear that you actually want to perform this type of join.




Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net