|
Custom Search
| |
|
Use Cross Join to Combine Data in MySQL In the real world, CROSS JOIN is used when you need to find out all the possibilities of combining two tables where the result set includes every row from each contributing table. CROSS JOIN (without a WHERE clause or ON clause in join condition) produces a result set where its size is the number of rows in the first table multiplying the number of rows in the second table. This type of result is called Cartesian Product of two tables (Table 1 x Table 2). A practical example of CROSS JOIN is to build a junk dimension in a data warehouse environment where all the combination possibilities need to be worked out and stored. Let's visually illustrate. Say we have two tables cross-joined together as below.
Because there are 3 rows in each table so cross join produces 9 rows.
When using CROSS JOIN in MySQL, pay attention to the following guidelines.
In example below, Products table cross joins with Suppliers table to work out how many ways that a product can be provided by a supplier. The result has total 2,233 rows because Products table has 77 rows and Suppliers table has 29 rows, 77 x 29 = 2,233. select t1.ProductID, t1.ProductName, t2.CompanyName as SupplierName
Query result set - 2,233 rows returned:
However, if you add ON clause in the CROSS JOIN, it becomes an INNER JOIN. select t1.ProductID, t1.ProductName, t2.CompanyName as SupplierName
Query result set - 77 rows returned:
Happy Coding!
Other tutorials in this category 1. Using Inner Joins to Combine Data from Two Tables |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |