1. Example as below:
# 1. Goods table
select * from goods;
+----------+--------+------------+
| goods_id | cat_id | goods_name |
+----------+--------+------------+
| 1 | 1 | CDMA Phone |
| 2 | 1 | GSM Phone |
| 3 | 1 | 3G Phone |
+----------+--------+------------+
# 2. Cat table
select * from cat;
+--------+--------------+
| cat_id | cat_name |
+--------+--------------+
| 1 | Mobile Phone |
+--------+--------------+
# 3. How to combine cat_name into goods table?
# First approach:
# Using JAVA, select all from goods and get cat_id, then search cat table and get cat_name. Combine results.
# Using m*n times SQL
# Second approach:
# Using join(left join)
# select goods_id, goods_name, cat.cat_name from *** where goods.cat_id = cat.cat_id;
# But how to realize this?
2. Set/Collection in Math
1) Properties of Set: Orderless, Uniquity
# Orderless
#{1, 2, 3} == {2, 3, 1} == {1, 3, 2}
# Uniquity
# {1, 2, 3, 3} is not a Set
2) Regard a table as a set, and a row in a table as an element in a set.
So seriously speaking, there can not be two same rows in a single table.
But actually, there can be two same rows in a single table because there is a rowId for each row in a table. And the rowId is not visible. It is generated by MySQL automatically and can not be accessed by user.
3. Cartesian Product in Math
# Eg 1
# What's the cartesian product of the two following sets?
# Q: {2, 3, 4} X {4, 7}
# A: {8, 14, 12, 21, 16, 28}--->Wrong!
# A: {{2, 4}, {2, 7}, {3, 4}, {3, 7}, {4, 4}, {4, 7}}--->Bingo!
#Eg 2
# What's the cartesian product of the two following sets
# Q: {chicken, duck} X {cat, dog, camel}
# A: {{chicken, cat}, {chicken, dog}, {chicken, camel}, {duck, cat}, {duck, dog}, {duck, camel}}
1) Cartesian product is also called Set multiple.
2) Cartesian product is actually a total combination of the two sets.
Q: Let set A have M elements which are unique in set A; Let set B have N elements which are unique in set B. So how many element will we get after cartesian product of A and B?
A: The number is M*N. And if we put these M*N elements in a set there will not be duplicate elements to worry about
. Q: If table A have 9 columns and table B have 5 columns. What's the cartesian product of the two tables?
A: There will be 9*5=45 columns.
Eg:
Cartesian product of two tables (Refer from IBM red brick warehouse website)
Join of two tables
State table | Region table | |||
---|---|---|---|---|
City | State | City | Area | |
Jacksonville | FL | Jacksonville | South | |
Miami | FL | Miami | South | |
Nashville | TN | New Orleans | South |
Example query
select * from state, region;
Cartesian product (join predicate not specified)
City | State | City | Area |
---|---|---|---|
Jacksonville | FL | Jacksonville | South |
Jacksonville | FL | Miami | South |
Jacksonville | FL | New Orleans | South |
Miami | FL | Jacksonville | South |
Miami | FL | Miami | South |
Miami | FL | New Orleans | South |
Nashville | TN | Jacksonville | South |
Nashville | TN | Miami | South |
Nashville | TN | New Orleans | South |
Eg
#Cartesian product of two tables
select * from goods, cat;
+----------+--------+------------+--------+--------------+
| goods_id | cat_id | goods_name | cat_id | cat_name |
+----------+--------+------------+--------+--------------+
| 1 | 1 | CDMA Phone | 1 | Mobile Phone |
| 2 | 1 | GSM Phone | 1 | Mobile Phone |
| 3 | 1 | 3G Phone | 1 | Mobile Phone |
+----------+--------+------------+--------+--------------+
#A possible solution to the question raised at begining
select * from goods, cat where goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+--------------+
| goods_id | cat_id | goods_name | cat_id | cat_name |
+----------+--------+------------+--------+--------------+
| 1 | 1 | CDMA Phone | 1 | Mobile Phone |
| 2 | 1 | GSM Phone | 1 | Mobile Phone |
| 3 | 1 | 3G Phone | 1 | Mobile Phone |
+----------+--------+------------+--------+--------------+
#Two Steps:
#1. Get cartesian product of two tables
#2. Apply filters to the result set.
#A more elegant way by using left join
select * from goods left join cat on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+--------------+
| goods_id | cat_id | goods_name | cat_id | cat_name |
+----------+--------+------------+--------+--------------+
| 1 | 1 | CDMA Phone | 1 | Mobile Phone |
| 2 | 1 | GSM Phone | 1 | Mobile Phone |
| 3 | 1 | 3G Phone | 1 | Mobile Phone |
+----------+--------+------------+--------+--------------+
#Remove duplicate columns
select goods.cat_id, goods_id, goods_name from goods left join cat on goods.cat_id = cat.cat_id;
+--------+----------+------------+
| cat_id | goods_id | goods_name |
+--------+----------+------------+
| 1 | 1 | CDMA Phone |
| 1 | 2 | GSM Phone |
| 1 | 3 | 3G Phone |
+--------+----------+------------+
1) Regard the result of cartesian product or left join as a real table and not as a result set.
2) We can use where, group by, having, order by, limit on this result table.
Eg.
select goods.cat_id, goods_id, goods_name from goods left join cat on goods.cat_id = cat.cat_id where goods_id >= 2;
+--------+----------+------------+
| cat_id | goods_id | goods_name |
+--------+----------+------------+
| 1 | 2 | GSM Phone |
| 1 | 3 | 3G Phone |
+--------+----------+------------+