select A.fldA, A.fldB from tableA A where A.fldKey IN
(
  select AA.fldKey from tableA AA inner join tableB B ON
  AA.fldKey = B.fldKey inner join tableC C ON B.fldChild = C.fldChild
)
OR A.fldKey in
(
  select AAA.fldKey from tableA AAA inner join tableX X ON
  AAA.fldKey = X.fldKey inner join tableC C ON X.fldChild = C.fldChild
)

select A.fldA, A.fldB from tableA A where A.fldKey IN
(
  select AA.fldKey from tableA AA inner join tableB B ON
  AA.fldKey = B.fldKey inner join tableC C ON B.fldChild = C.fldChild
)
OR A.fldKey = 7

set statistics io OFF
set nocount on

CREATE TABLE #foo (
  fldID int identity(1, 1), 
  fldNum int not null
)

-- Get a few records started
insert into #foo(fldNum) 
  select 1 as X union all
  select 1 as X union all
  select 1 as X union all
  select 1 as X union all
  select 1 as X union all
  select 1 as X union all
  select 1 as X 



while (select count(fldID) from #foo) < 100000
  insert into #foo(fldNum) select fldID from #foo

select count(fldID) from #foo

set statistics io ON

-- Get statistics on sub query
select top 500 fldID from #foo
-- Results Table #foo Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.


-- Get statistics on the main query and the sub query
select A.fldID from #foo A where A.fldID IN
(
  select top 500 B.fldID from #foo B
)
-- Results Table #foo Scan count 2, logical reads 256, physical reads 0, read-ahead reads 0.


-- Get statistics on the second condition
select A.fldID from #foo A where A.fldID = 1
-- Results Table #foo Scan count 1, logical reads 254, physical reads 0, read-ahead reads 0.


-- Get statistics on the whole thing
select A.fldID from #foo A where A.fldID IN
(
  select top 500 B.fldID from #foo B
) OR A.fldID = 1

-- Results Table #foo Scan count 3, logical reads 256, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 498, logical reads 997, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 500, logical reads 1001, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.

select A.fldA, A.fldB from tableA A 
where exists(select * from tableA AA inner join tableB B ON AA.fldKey = B.fldKey 
inner join tableC C ON B.fldChild = C.fldChild
where A.fldKey=AA.fldKey)
OR exists(select * from tableA AAA inner join tableX X ON AAA.fldKey = X.fldKey 
inner join tableC C ON X.fldChild = C.fldChild
where A.fldKey=AAA.fldKey)

quote:

---

Originally posted by X002548

why not just do the joins and a union?

---