SQL CROSS JOIN
最近在講到T-SQL查詢的Join部分時,一下子沒有想起來CROSS JOIN的用法,因為其實平常也確實基本不用到。特意找了一個例子,以供參考
CROSS JOIN又稱為笛卡爾乘積,實際上是把兩個表乘起來。以下資料摘自:http://www.sqlguides.com/sql_cross_join.php
SQL CROSS JOIN will return all records where each row from the first table is combined with each row from the second table. Which also mean CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables.
A CROSS JOIN can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.
SQL CROSS JOIN syntax:
SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]
OR
SELECT * FROM [TABLE 1], [TABLE 2]
EXAMPLE :
Let's try with 2 tables below:
Table 1: GameScores
| PlayerName | DepartmentId | Scores |
| Jason | 1 | 3000 |
| Irene | 1 | 1500 |
| Jane | 2 | 1000 |
| David | 2 | 2500 |
| Paul | 3 | 2000 |
| James | 3 | 2000 |
Table 2: Departments
| DepartmentId | DepartmentName |
| 1 | IT |
| 2 | Marketing |
| 3 | HR |
SQL statement :
SELECT* FROM GameScores CROSS JOIN Departments
Result:
?
| PlayerName | DepartmentId | Scores | DepartmentId | DepartmentName |
| Jason | 1 | 3000 | 1 | IT |
| Irene | 1 | 1500 | 1 | IT |
| Jane | 2 | 1000 | 1 | IT |
| David | 2 | 2500 | 1 | IT |
| Paul | 3 | 2000 | 1 | IT |
| James | 3 | 2000 | 1 | IT |
| Jason | 1 | 3000 | 2 | Marketing |
| Irene | 1 | 1500 | 2 | Marketing |
| Jane | 2 | 1000 | 2 | Marketing |
| David | 2 | 2500 | 2 | Marketing |
| Paul | 3 | 2000 | 2 | Marketing |
| James | 3 | 3000 | 2 | Marketing |
| Jason | 1 | 3000 | 3 | HR |
| Irene | 1 | 1500 | 3 | HR |
| Jane | 2 | 1000 | 3 | HR |
| David | 2 | 2500 | 3 | HR |
| Paul | 3 | 2000 | 3 | HR |
| James | 3 | 3000 | 3 | HR |
總結
以上是生活随笔為你收集整理的SQL CROSS JOIN的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 关于规则引擎一些基本理论的积累
- 下一篇: 外星人怎么设置u盘启动盘 外星人如何制作