PostgreSQL 交叉连接
交叉连接
这个CROSS JOIN关键字将 "left" 表中的所有记录与 "right" 表中的每条记录进行匹配。
这意味着 "right" 表中的所有记录都将针对 "left" 表中的每条记录返回。
这种连接方式可能会返回非常大的表，如果不需要，就不应该使用它。
让我们看一个使用我们的虚拟对象的例子testproducts表格：
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)
我们将努力加入testproducts表与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)
笔记：这个CROSS JOIN方法将返回全部categories对于每个testproduct，这意味着它将返回 80 行 (10 * 8)。
示例
加入testproducts到 categories使用CROSS JOIN关键字：
SELECT testproduct_id, product_name, category_name
FROM testproducts
CROSS JOIN categories;
运行示例 »
结果
全部categories对于每个testproduct将被返回：
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)