THAM GIA CHÉO PostgreSQL
THAM GIA CHÉO
Từ khóa CROSS JOIN khớp TẤT CẢ các bản ghi từ bảng "trái" với MỖI bản ghi từ bảng "phải".
Điều đó có nghĩa là tất cả các bản ghi từ bảng "phải" sẽ được trả về cho mỗi bản ghi trong bảng "trái".
Cách nối này có khả năng trả về một bảng rất lớn và bạn không nên sử dụng nó nếu không cần thiết.
Hãy xem một ví dụ sử dụng bảng testproducts giả của chúng tôi:
testproduct_id | product_name
| category_id
----------------+------------------------+-------------
1 | Johns Fruit Cake |
3
2 | Marys Healthy Mix |
9
3 | Peters Scary Stuff |
10
4 | Jims Secret Recipe |
11
5 | Elisabeths Best Apples |
12
6 | Janes Favorite Cheese |
4
7 | Billys Home Made Pizza |
13
8 | Ellas Special Salmon |
8
9 | Roberts Rich Spaghetti |
5
10 | Mias Popular Ice |
14
(10 rows) Chúng ta sẽ cố gắng nối bảng testproducts với bảng categories :
category_id | category_name |
description
-------------+----------------+------------------------------------------------------------
1 | Beverages | Soft drinks, coffees, teas,
beers, and ales
2 | Condiments | Sweet and savory sauces, relishes,
spreads, and seasonings
3 | Confections | Desserts, candies, and sweet breads
4 | Dairy Products | Cheeses
5 | Grains/Cereals | Breads, crackers, pasta, and cereal
6 | Meat/Poultry | Prepared meats
7 | Produce | Dried fruit and bean
curd
8 |
Seafood | Seaweed and fish
(8
rows) Lưu ý: Phương thức CROSS JOIN sẽ trả về TẤT CẢ categories cho MỖI testproduct , nghĩa là nó sẽ trả về 80 hàng (10 * 8).
Ví dụ
Nối testproducts vào categories bằng cách sử dụng từ khóa CROSS JOIN :
SELECT
testproduct_id, product_name, category_name
FROM testproducts
CROSS JOIN categories;
Chạy ví dụ »Kết quả
Tất cả categories cho mỗi testproduct sẽ được trả về:
testproduct_id
| product_name |
category_name
----------------+------------------------+----------------
1 | Johns Fruit Cake | Beverages
1 | Johns Fruit Cake | Condiments
1 | Johns Fruit Cake | Confections
1 | Johns Fruit Cake | Dairy Products
1 | Johns Fruit Cake | Grains/Cereals
1 | Johns Fruit Cake | Meat/Poultry
1 | Johns Fruit Cake | Produce
1 | Johns Fruit Cake | Seafood
2 | Marys Healthy Mix | Beverages
2 | Marys Healthy Mix | Condiments
2 | Marys Healthy Mix | Confections
2 | Marys Healthy Mix | Dairy Products
2 | Marys Healthy Mix | Grains/Cereals
2 | Marys Healthy Mix | Meat/Poultry
2 | Marys Healthy Mix | Produce
2 | Marys Healthy Mix | Seafood
3 | Peters Scary Stuff | Beverages
3 | Peters Scary Stuff | Condiments
3 | Peters Scary Stuff | Confections
3 | Peters Scary Stuff | Dairy Products
3 | Peters Scary Stuff | Grains/Cereals
3 | Peters Scary Stuff | Meat/Poultry
3 | Peters Scary Stuff | Produce
3 | Peters Scary Stuff | Seafood
4 | Jims Secret Recipe | Beverages
4 | Jims Secret Recipe | Condiments
4 | Jims Secret Recipe | Confections
4 | Jims Secret Recipe | Dairy Products
4 | Jims Secret Recipe | Grains/Cereals
4 | Jims Secret Recipe | Meat/Poultry
4 | Jims Secret Recipe | Produce
4 | Jims Secret Recipe | Seafood
5 | Elisabeths Best Apples | Beverages
5 | Elisabeths Best Apples | Condiments
5 | Elisabeths Best Apples | Confections
5 | Elisabeths Best Apples | Dairy Products
5 | Elisabeths Best Apples | Grains/Cereals
5 | Elisabeths Best Apples | Meat/Poultry
5 | Elisabeths Best Apples | Produce
5 | Elisabeths Best Apples | Seafood
6 | Janes Favorite Cheese | Beverages
6 | Janes Favorite Cheese | Condiments
6 | Janes Favorite Cheese | Confections
6 | Janes Favorite Cheese | Dairy Products
6 | Janes Favorite Cheese | Grains/Cereals
6 | Janes Favorite Cheese | Meat/Poultry
6 | Janes Favorite Cheese | Produce
6 | Janes Favorite Cheese | Seafood
7 | Billys Home Made Pizza | Beverages
7 | Billys Home Made Pizza | Condiments
7 | Billys Home Made Pizza | Confections
7 | Billys Home Made Pizza | Dairy Products
7 | Billys Home Made Pizza | Grains/Cereals
7 | Billys Home Made Pizza | Meat/Poultry
7 | Billys Home Made Pizza | Produce
7 | Billys Home Made Pizza | Seafood
8 | Ellas Special Salmon | Beverages
8 | Ellas Special Salmon | Condiments
8 | Ellas Special Salmon | Confections
8 | Ellas Special Salmon | Dairy Products
8 | Ellas Special Salmon | Grains/Cereals
8 | Ellas Special Salmon | Meat/Poultry
8 | Ellas Special Salmon | Produce
8 | Ellas Special Salmon | Seafood
9 | Roberts Rich Spaghetti | Beverages
9 | Roberts Rich Spaghetti | Condiments
9 | Roberts Rich Spaghetti | Confections
9 | Roberts Rich Spaghetti | Dairy Products
9 | Roberts Rich Spaghetti | Grains/Cereals
9 | Roberts Rich Spaghetti | Meat/Poultry
9 | Roberts Rich Spaghetti | Produce
9 | Roberts Rich Spaghetti | Seafood
10 | Mias Popular Ice | Beverages
10 | Mias Popular Ice | Condiments
10 | Mias Popular Ice | Confections
10 | Mias Popular Ice | Dairy Products
10 | Mias Popular Ice | Grains/Cereals
10 | Mias Popular Ice | Meat/Poultry
10 | Mias Popular Ice | Produce
10 | Mias Popular Ice | Seafood
(80
rows)
