Understanding Cartesian Product in SQL
I can't figure out how cartesian product works. Let's consider a simple scheme:
mysql> select * from account;
+----------------+-------------+---------+
| account_number | branch_name | balance |
+----------------+-------------+---------+
| A101 | Downtown | 500 |
| A102 | Perryridge | 400 |
| A201 | Brighton | 900 |
| A215 | Mianus | 700 |
| A217 | Brighton | 750 |
| A222 | Redwood | 700 |
| A305 | Round Hill | 350 |
+----------------+-------------+---------+
7 rows in set (0.00 sec)
Now when I ask the request
select a.balance from account a, account b where a.balance<b.balance;
I get a series of values besides the maximum value of 900. Then, using an operator not in
, I determine the maximum value. Before that, in the above query, when a join occurs based on a condition a.balance<b.balance
, the first tuple in the relation should be 500
. In theory, the first 5 values should be:
500 500 500 500 400
But I am getting:
+---------+
| balance |
+---------+
| 400 |
| 350 |
| 350 |
| 500 |
| 400 |
How it works? I am using MySQL database.
source to share
Cartesian join concatenates every record in the first table with every record in the second table, since your table has 7 rows and it is concatenated with itself, it should return 49 records if you didn't have a sentence where
. The where clause only allows records where the balance is a
less than b
. Since 900
- this is, as you said, the maximum balance in the table, it will never be less than any other balance, and therefore it will never be returned.
For the first five lines, the usual SQL rules apply to joins as well. Since SQL tables have no internal ordering, it is entirely up to the database to decide how to get them back unless you explicitly specify the order in the clause order by
. The values you provided are perfectly valid values that you expect the query to return.
source to share