34,576
社区成员
发帖
与我相关
我的任务
分享cross join疑问
cross join:
The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
我通过select * 获取cross join 结果只有12行，由count（*）为4*4=16可知 返回的并不是所有结果集。
1. 为什么？
2. 12行是怎么算出来的？
3. 执行计划里边 inner join的警告星系"No join predicate"该怎么理解？
4. 怎么才能获取所有行？
The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
我通过select * 获取cross join 结果只有12行，由count（*）为4*4=16可知 返回的并不是所有结果集。
1. 为什么？
2. 12行是怎么算出来的？
3. 执行计划里边 inner join的警告星系"No join predicate"该怎么理解？
4. 怎么才能获取所有行？
--自然数表1-1M
if object_id('Nums') is not null
drop table Nums
go
CREATE TABLE Nums(n int NOT NULL)
go
insert into Nums
select 1
union all select 2
--select a.n from Nums a
select * from
(select n=1 from Nums a cross join Nums b) c
cross join
(select n=1 from Nums a cross join Nums b) d
/*
n n
----------- -----------
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
(12 行受影响)
*/
select count(*),count(1) from
(select n=1 from Nums a cross join Nums b) c
cross join
(select n=1 from Nums a cross join Nums b) d
/*
----------- -----------
16 16
(1 行受影响)
*/
...全文
请发表友善的回复…
发表回复
TheGodOfGods 2010-07-30
- 打赏
- 举报
啊TheGodOfGods 2010-07-30
- 打赏
- 举报
啊
TheGodOfGods 2010-07-30
- 打赏
- 举报
啊
claro 2010-07-21
- 打赏
- 举报
不了解
帮顶
帮顶
TheGodOfGods 2010-07-21
- 打赏
- 举报
找到原因了。
set rowcount()选项给了非零值（12），估计是滑轮不小心放上边滚动了下
set rowcount()选项给了非零值（12），估计是滑轮不小心放上边滚动了下
TheGodOfGods 2010-07-21
- 打赏
- 举报
工具--->选项--->查询执行-->重置为默认值
现在运行就正常了
可惜没法还原现场了，不知道动了哪个设置
现在运行就正常了
可惜没法还原现场了，不知道动了哪个设置
claro 2010-07-21
- 打赏
- 举报
;with a as (
select 1 a
union all select 2
),
b as (
select 1 a
union all select 2
)
select * from
(select n=1 from a cross join b) c
join --非cross
(select n=1 from a cross join b) d on 1=1
--result:
n n
----------- -----------
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
(16 行受影响)
claro 2010-07-21
- 打赏
- 举报
--幻像
;with a as (
select 1 a
union all select 2
),
b as (
select 1 a
union all select 2
)
select * from a cross join b
--result:
a a
----------- -----------
1 1
1 2
2 1
2 2
(4 行受影响)TheGodOfGods 2010-07-21
- 打赏
- 举报
这能看出哪有问题不？ 望大侠指点
TheGodOfGods 2010-07-21
- 打赏
- 举报
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------ ------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------- ------------------ ---------------------------------------------------------------- -------- ------------------
select * from
(select n=1 from Nums a cross join Nums b) c
cross join
(select n=1 from Nums a cross join Nums b) d 1 1 0 NULL NULL 1 NULL 12 NULL NULL NULL 0.01324375 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE:([Expr1006]=(1), [Expr1013]=(1))) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1006]=(1), [Expr1013]=(1)) [Expr1006]=(1), [Expr1013]=(1) 12 0 1.6E-06 15 0.01324375 [Expr1006], [Expr1013] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join) 1 3 2 Nested Loops Inner Join NULL NULL 12 0 6.688E-05 9 0.01324255 NULL NO JOIN PREDICATE PLAN_ROW 0 1
|--Nested Loops(Inner Join) 1 4 3 Nested Loops Inner Join NULL NULL 6 0 3.344E-05 9 0.00989616 NULL NO JOIN PREDICATE PLAN_ROW 0 1
| |--Nested Loops(Inner Join) 1 5 4 Nested Loops Inner Join NULL NULL 3 0 1.672E-05 9 0.006581673 NULL NO JOIN PREDICATE PLAN_ROW 0 1
| | |--Table Scan(OBJECT:([Micropoint].[dbo].[Nums] AS [a])) 1 6 5 Table Scan Table Scan OBJECT:([Micropoint].[dbo].[Nums] AS [a]) NULL 1.5 0.003125 0.0001592 9 0.00328365 NULL NULL PLAN_ROW 0 1
| | |--Table Scan(OBJECT:([Micropoint].[dbo].[Nums] AS [a])) 1 7 5 Table Scan Table Scan OBJECT:([Micropoint].[dbo].[Nums] AS [a]) NULL 2 0.0032035 8.07E-05 9 0.00328585 NULL NULL PLAN_ROW 0 1.75
| |--Table Scan(OBJECT:([Micropoint].[dbo].[Nums] AS [b])) 1 8 4 Table Scan Table Scan OBJECT:([Micropoint].[dbo].[Nums] AS [b]) NULL 2 0.0032035 8.07E-05 9 0.00328915 NULL NULL PLAN_ROW 0 3.25
|--Table Scan(OBJECT:([Micropoint].[dbo].[Nums] AS [b])) 1 9 3 Table Scan Table Scan OBJECT:([Micropoint].[dbo].[Nums] AS [b]) NULL 2 0.0032035 8.07E-05 9 0.00329575 NULL NULL PLAN_ROW 0 6.25
(9 行受影响)
我的EstimateRows列居然有小数。。。
永生天地 2010-07-21
- 打赏
- 举报
[Quote=引用 3 楼 thegodofgods 的回复:]
晕了啊，屡试不爽，还是跟以前结果一样，咋回事呢。。。
下边是版本信息
Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft 数据访问组件 (MDAC) 2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML 2.6 3.0 5.0 6.0
Mic……
[/Quote]
那些ie framework都是干什么
晕了啊，屡试不爽，还是跟以前结果一样，咋回事呢。。。
下边是版本信息
Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft 数据访问组件 (MDAC) 2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML 2.6 3.0 5.0 6.0
Mic……
[/Quote]
那些ie framework都是干什么
永生天地 2010-07-21
- 打赏
- 举报
set showplan_all on
select * from
(select n=1 from Nums a cross join Nums b) c
cross join
(select n=1 from Nums a cross join Nums b) d
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------- ------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------- ------------------ ---------------------------------------------------------------- -------- ------------------
select * from
(select n=1 from Nums a cross join Nums b) c
cross join
(select n=1 from Nums a cross join Nums b) d 1 1 0 NULL NULL 1 NULL 16 NULL NULL NULL 0.01327964 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE:([Expr1006]=(1), [Expr1013]=(1))) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1006]=(1), [Expr1013]=(1)) [Expr1006]=(1), [Expr1013]=(1) 16 0 1.6E-06 15 0.01327964 [Expr1006], [Expr1013] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join) 1 3 2 Nested Loops Inner Join NULL NULL 16 0 6.688E-05 9 0.01327804 NULL NO JOIN PREDICATE PLAN_ROW 0 1
|--Nested Loops(Inner Join) 1 4 3 Nested Loops Inner Join NULL NULL 8 0 3.344E-05 9 0.00991156 NULL NO JOIN PREDICATE PLAN_ROW 0 1
| |--Nested Loops(Inner Join) 1 5 4 Nested Loops Inner Join NULL NULL 4 0 1.672E-05 9 0.00658732 NULL NO JOIN PREDICATE PLAN_ROW 0 1
| | |--Table Scan(OBJECT:([master].[dbo].[Nums] AS [a])) 1 6 5 Table Scan Table Scan OBJECT:([master].[dbo].[Nums] AS [a]) NULL 2 0.003125 0.0001592 9 0.0032842 NULL NULL PLAN_ROW 0 1
| | |--Table Scan(OBJECT:([master].[dbo].[Nums] AS [a])) 1 7 5 Table Scan Table Scan OBJECT:([master].[dbo].[Nums] AS [a]) NULL 2 0.0032035 8.07E-05 9 0.0032864 NULL NULL PLAN_ROW 0 2
| |--Table Scan(OBJECT:([master].[dbo].[Nums] AS [b])) 1 8 4 Table Scan Table Scan OBJECT:([master].[dbo].[Nums] AS [b]) NULL 2 0.0032035 8.07E-05 9 0.0032908 NULL NULL PLAN_ROW 0 4
|--Table Scan(OBJECT:([master].[dbo].[Nums] AS [b])) 1 9 3 Table Scan Table Scan OBJECT:([master].[dbo].[Nums] AS [b]) NULL 2 0.0032035 8.07E-05 9 0.0032996 NULL NULL PLAN_ROW 0 8
(9 行受影响)TheGodOfGods 2010-07-21
- 打赏
- 举报
晕了啊，屡试不爽，还是跟以前结果一样，咋回事呢。。。
下边是版本信息
Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft 数据访问组件 (MDAC) 2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework 2.0.50727.3053
操作系统 5.1.2600
下边是版本信息
Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft 数据访问组件 (MDAC) 2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework 2.0.50727.3053
操作系统 5.1.2600
永生天地 2010-07-21
- 打赏
- 举报
if object_id('Nums') is not null
drop table Nums
go
CREATE TABLE Nums(n int NOT NULL)
go
insert into Nums
select 1
union all select 2
--select a.n from Nums a
select * from
(select n=1 from Nums a cross join Nums b) c
cross join
(select n=1 from Nums a cross join Nums b) d
/*
n n
----------- -----------
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
(16 行受影响)
*/
天-笑 2010-07-21
- 打赏
- 举报
if object_id('Nums') is not null
drop table Nums
go
CREATE TABLE Nums(n int NOT NULL)
go
insert into Nums
select 1
union all select 2
--select a.n from Nums a
select * from
(select n=1 from Nums a cross join Nums b) c
cross join
(select n=1 from Nums a cross join Nums b) d
(2 行受影响)
n n
----------- -----------
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
(16 行受影响)
