join or subselect: common

Showing posts with label common. Show all posts

Wednesday, March 21, 2012

Joining two large queries as derived tables

All,
I want to join two large queries together keying on a common column. I
have simplifed the queries / derived tables; but I am trying to do
something like what I have below. How do you join two derived tables?
SELECT * FROM
(SELECT Name AS 'Name1', ... other columns
FROM [TABLE1]) AS a
JOIN
SELECT * FROM
(SELECT Name AS 'Name2', ... other columns
FROM [TABLE2]) AS b
ON a.Name = b.NameTry,
SELECT *
FROM
(SELECT Name AS 'Name1', ... other columns FROM [TABLE1]) AS a
inner JOIN
(SELECT Name AS 'Name2', ... other columns FROM [TABLE2]) AS b
ON a.Name = b.Name
go
AMB
"dpless@.gmail.com" wrote:

> All,
> I want to join two large queries together keying on a common column. I
> have simplifed the queries / derived tables; but I am trying to do
> something like what I have below. How do you join two derived tables?
>
> SELECT * FROM
> (SELECT Name AS 'Name1', ... other columns
> FROM [TABLE1]) AS a
> JOIN
> SELECT * FROM
> (SELECT Name AS 'Name2', ... other columns
> FROM [TABLE2]) AS b
> ON a.Name = b.Name
>|||Is this what you are trying to do?
SELECT * FROM
(SELECT * FROM table1) a
INNER JOIN (select * from table2) b ON a.name=b.name
-- or --
SELECT * FROM
(SELECT * FROM dailychallenge) a,
(SELECT * FROM DailyChallengeResponse) b
WHERE a.challengedate=b.challengedate
Let us know how you make out.
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--

Joining to Huge Table

Do anybody know how to get data from very small and huge table based on
common column(ex : Stae_Cd).Index on common column is not useful.
Very small table has lookup table (static data). Please suggest .
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd = HT.State_cd and
INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
Thanks in advance.Why are you joining to your VerySmall_Table1 twice?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>|||Table design is like that.Two tables has same column with other info.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> Why are you joining to your VerySmall_Table1 twice?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > Do anybody know how to get data from very small and huge table based on
> > common column(ex : Stae_Cd).Index on common column is not useful.
> > Very small table has lookup table (static data). Please suggest .
> >
> >
> >
> > SELECT DISTINCT
> >
> > VS1.coverage_id,
> > HT.Col_id,
> > VS2.Col3
> >
> > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
VS.State_Cd
> => > HT.State_cd and
> >
> > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd =HT.State_cd
> >
> > Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
> >
> >
> > Thanks in advance.
> >
> >
>|||But you're joining on the same columns... Both joins are VS.State_Cd =HT.State_cd. Why not just join once?
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Table design is like that.Two tables has same column with other info.
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> > Why are you joining to your VerySmall_Table1 twice?
> >
> >
> > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > > Do anybody know how to get data from very small and huge table based
on
> > > common column(ex : Stae_Cd).Index on common column is not useful.
> > > Very small table has lookup table (static data). Please suggest .
> > >
> > >
> > >
> > > SELECT DISTINCT
> > >
> > > VS1.coverage_id,
> > > HT.Col_id,
> > > VS2.Col3
> > >
> > > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
> VS.State_Cd
> > => > > HT.State_cd and
> > >
> > > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd => HT.State_cd
> > >
> > > Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
> > >
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>|||I also do not understand joining the small table twice... see if this works
Did you ever work for Unisys/Burroughs in Charlotte, NC?
SELECT DISTINCT
VS1.coverage_id,
HT.Col_id,
VS2.Col3
FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd =HT.State_cd
Where HT.Col_id = 123456789
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> Do anybody know how to get data from very small and huge table based on
> common column(ex : Stae_Cd).Index on common column is not useful.
> Very small table has lookup table (static data). Please suggest .
>
> SELECT DISTINCT
> VS1.coverage_id,
> HT.Col_id,
> VS2.Col3
> FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON VS.State_Cd
=> HT.State_cd and
> INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd = HT.State_cd
> Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
>
> Thanks in advance.
>|||OK, Let me tell in other way.
Is there any way that table level desing can help in retrieving data faster
while joining big table with small lookup table, lookup table has just 20
recordsl .
Table1( ID int Primary Key clustered,
Type_ID int Foreign key
.......
)
Table2 ( Type_ID int Primary Key
...............
)
Table1 has 30 million records
and Table2 has just 10 records.
select Table1.*
from table1,table2
where table1.type_id = table2.type_id
Is the above table design is good, or is there anyother design for this.
Thanks.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
> But you're joining on the same columns... Both joins are VS.State_Cd => HT.State_cd. Why not just join once?
>
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> > Table design is like that.Two tables has same column with other info.
> > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> message
> > news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> > > Why are you joining to your VerySmall_Table1 twice?
> > >
> > >
> > > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > > news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > > > Do anybody know how to get data from very small and huge table based
> on
> > > > common column(ex : Stae_Cd).Index on common column is not useful.
> > > > Very small table has lookup table (static data). Please suggest .
> > > >
> > > >
> > > >
> > > > SELECT DISTINCT
> > > >
> > > > VS1.coverage_id,
> > > > HT.Col_id,
> > > > VS2.Col3
> > > >
> > > > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
> > VS.State_Cd
> > > => > > > HT.State_cd and
> > > >
> > > > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd => > HT.State_cd
> > > >
> > > > Where HT.Col_id = 123456789 -- HT.Col_id is clustered index.
> > > >
> > > >
> > > > Thanks in advance.
> > > >
> > > >
> > >
> > >
> >
> >
>|||What you're talking about is very typical in data warehousing scenarios
where Table1 would represent the fact table and Table2 would represent the
dimension table. What you're missing that's almost always, if not always
present in a DW, is further filtration of the dimensions, in order to filter
the facts. Executing the query you've posted will, in essence, be the same
as doing:
SELECT * FROM Table1
So what you need is something more along the lines of:
select Table1.*
from table1
join table2 on table1.type_id = table2.type_id
where table2.type_desc = 'some description'
... otherwise, there's no reason for the JOIN to begin with. Note I've
converted your query to SQL-92 syntax; just my personal preference.
As for indexing, if your dimension table has only 10 rows, clustering the
fact (big) table by that column will greatly improve select performance as
it will allow the disc to read from one contiguous block when selecting any
of the given 10 choices; however, I don't know what OTHER dimensions
(lookups) you might have that a cluster might serve better, so you'll have
to look at your schema or perhaps try out the Index Tuning Wizard to help
you with those decisions.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:ex4hR1lIEHA.2556@.TK2MSFTNGP12.phx.gbl...
> OK, Let me tell in other way.
> Is there any way that table level desing can help in retrieving data
faster
> while joining big table with small lookup table, lookup table has just 20
> recordsl .
> Table1( ID int Primary Key clustered,
> Type_ID int Foreign key
> .......
> )
> Table2 ( Type_ID int Primary Key
> ...............
> )
>
> Table1 has 30 million records
> and Table2 has just 10 records.
> select Table1.*
> from table1,table2
> where table1.type_id = table2.type_id
>
> Is the above table design is good, or is there anyother design for this.
>
> Thanks.
>
> "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
message
> news:eAN8zLbIEHA.940@.tk2msftngp13.phx.gbl...
> > But you're joining on the same columns... Both joins are VS.State_Cd => > HT.State_cd. Why not just join once?
> >
> >
> > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > news:eosBH9aIEHA.2876@.TK2MSFTNGP09.phx.gbl...
> > > Table design is like that.Two tables has same column with other info.
> > > "Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in
> > message
> > > news:ep40LjaIEHA.3536@.TK2MSFTNGP09.phx.gbl...
> > > > Why are you joining to your VerySmall_Table1 twice?
> > > >
> > > >
> > > > "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> > > > news:#0fI#BaIEHA.348@.tk2msftngp13.phx.gbl...
> > > > > Do anybody know how to get data from very small and huge table
based
> > on
> > > > > common column(ex : Stae_Cd).Index on common column is not useful.
> > > > > Very small table has lookup table (static data). Please suggest
.
> > > > >
> > > > >
> > > > >
> > > > > SELECT DISTINCT
> > > > >
> > > > > VS1.coverage_id,
> > > > > HT.Col_id,
> > > > > VS2.Col3
> > > > >
> > > > > FROM Huge_Table HT Inner Join VerySmall_Table1 VS1 ON
> > > VS.State_Cd
> > > > => > > > > HT.State_cd and
> > > > >
> > > > > INNER JOIN ON VerySmall_Table1 VS2 ON VS.State_Cd => > > HT.State_cd
> > > > >
> > > > > Where HT.Col_id = 123456789 -- HT.Col_id is clustered
index.
> > > > >
> > > > >
> > > > > Thanks in advance.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Monday, March 12, 2012

Joing two tables but avoid cartesian product

Hi all, I have two tables that don't have any common data:
[Table1]
Column11 Int
AnotherColumn Int
[Table2]
Column21 Int
Data:
[Table1]
Column11 | AnotherColumn
111 | 8
112 | 8
113 | 8
114 | 8
[Table2]
Column21
211
212
213
214
I need to join them, to get a rowset that looks like this:
Column11 | Column21
111 | 211
112 | 212
113 | 213
114 | 214
When I try to join them, I use one of these two SQL Statements:
Select
Column11,
Column21
From Table2
Inner Join Table1 On Table1.AnotherColumn = 8
Select
Column11,
Column21
From Table2, Table1
Where Table1.AnotherColumn = 8
But I get a cartesian product (which I don't want). How can I just "put one
column next to the other" in my resultset, without having a Cartesian
product?
Thanks in advance,
FrankSeems like there are no relations between the tables like a
parent-child relation. Therefore only a cartesian product will make
sense. (?!)
HTH, jens Suessmeyer.|||Does the data really look like this? If you are trying to "line up"
physical rows or in the order of insertion, there's no way to tell SQL
Server to correlate that. If you are trying to match up 11, 12, 13 and 14
as "belonging to the same row", then you can do something like this:
SET NOCOUNT ON
CREATE TABLE #Table1
(
Column11 Int,
AnotherColumn Int
)
CREATE TABLE #Table2
(
Column21 Int
)
INSERT #Table1
SELECT 111,8
UNION SELECT 112,8
UNION SELECT 113,8
UNION SELECT 114,8
INSERT #Table2
SELECT 211
UNION SELECT 212
UNION SELECT 213
UNION SELECT 214
SELECT
t1.Column11,
t2.Column21
FROM
#Table1 t1
INNER JOIN #Table2 t2
ON t1.Column11 % 100 = t2.Column21 % 100
WHERE
t1.AnotherColumn = 8
DROP TABLE #table1, #table2
If this is not what you're looking for, please provide better requirements.
See http://www.aspfaq.com/5006
"John Francisco Williams" <JohnFranciscoWilliams1010@.Yahoo.Com> wrote in
message news:erVNz9iEGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi all, I have two tables that don't have any common data:
> [Table1]
> Column11 Int
> AnotherColumn Int
> [Table2]
> Column21 Int
> Data:
> [Table1]
> Column11 | AnotherColumn
> 111 | 8
> 112 | 8
> 113 | 8
> 114 | 8
> [Table2]
> Column21
> 211
> 212
> 213
> 214
> I need to join them, to get a rowset that looks like this:
> Column11 | Column21
> 111 | 211
> 112 | 212
> 113 | 213
> 114 | 214
> When I try to join them, I use one of these two SQL Statements:
> Select
> Column11,
> Column21
> From Table2
> Inner Join Table1 On Table1.AnotherColumn = 8
> Select
> Column11,
> Column21
> From Table2, Table1
> Where Table1.AnotherColumn = 8
> But I get a cartesian product (which I don't want). How can I just "put
> one column next to the other" in my resultset, without having a Cartesian
> product?
> Thanks in advance,
> Frank
>|||not sure I understand what you really need, but try this:
create table #t1(i1 int primary key)
insert into #t1 values(123)
insert into #t1 values(124)
insert into #t1 values(125)
insert into #t1 values(126)
create table #t2(i2 int primary key)
insert into #t2 values(23)
insert into #t2 values(24)
insert into #t2 values(25)
insert into #t2 values(26)
insert into #t2 values(27)
select i1, i2 from
(select i1, (select count(*) from #t1 t11 where t11.i1<t1.i1) rn from
#t1 t1) t1
full outer join
(select i2, (select count(*) from #t2 t21 where t21.i2<t2.i2) rn from
#t2 t2) t2
on t1.rn=t2.rn
i1 i2
-- --
123 23
124 24
125 25
126 26
NULL 27
(5 row(s) affected)
on SQL Server 2005 you can use row_number() to calculate rn|||is it just a conincidence, or is it really that you want to match rows from
Table1 and Table2 in a way that 111 in Table1.Column11 matches 211 in
Table2.column22, 112 matches 212, etc? you can do something like this:
select *
from table1 t1 inner join table2 t2 on t1.column11%100=t2.column21%100
dean
"John Francisco Williams" <JohnFranciscoWilliams1010@.Yahoo.Com> wrote in
message news:erVNz9iEGHA.216@.TK2MSFTNGP15.phx.gbl...
> Hi all, I have two tables that don't have any common data:
> [Table1]
> Column11 Int
> AnotherColumn Int
> [Table2]
> Column21 Int
> Data:
> [Table1]
> Column11 | AnotherColumn
> 111 | 8
> 112 | 8
> 113 | 8
> 114 | 8
> [Table2]
> Column21
> 211
> 212
> 213
> 214
> I need to join them, to get a rowset that looks like this:
> Column11 | Column21
> 111 | 211
> 112 | 212
> 113 | 213
> 114 | 214
> When I try to join them, I use one of these two SQL Statements:
> Select
> Column11,
> Column21
> From Table2
> Inner Join Table1 On Table1.AnotherColumn = 8
> Select
> Column11,
> Column21
> From Table2, Table1
> Where Table1.AnotherColumn = 8
> But I get a cartesian product (which I don't want). How can I just "put
> one column next to the other" in my resultset, without having a Cartesian
> product?
> Thanks in advance,
> Frank
>|||and you can use PIVOT as well:
select [i1], [i2]
from (
select
row_number() over (order by i1) as rn,
'i1' as Src,
i1 as x
from #t1
union all
select
row_number() over (order by i2),
'i2',
i2
from #t2
) T PIVOT (
max(x) FOR Src in ([i1],[i2])
) as P
-- Steve Kass
-- Drew University
Alexander Kuznetsov wrote:

>not sure I understand what you really need, but try this:
>create table #t1(i1 int primary key)
>insert into #t1 values(123)
>insert into #t1 values(124)
>insert into #t1 values(125)
>insert into #t1 values(126)
>create table #t2(i2 int primary key)
>insert into #t2 values(23)
>insert into #t2 values(24)
>insert into #t2 values(25)
>insert into #t2 values(26)
>insert into #t2 values(27)
>select i1, i2 from
>(select i1, (select count(*) from #t1 t11 where t11.i1<t1.i1) rn from
>#t1 t1) t1
>full outer join
>(select i2, (select count(*) from #t2 t21 where t21.i2<t2.i2) rn from
>#t2 t2) t2
>on t1.rn=t2.rn
>
>i1 i2
>-- --
>123 23
>124 24
>125 25
>126 26
>NULL 27
>(5 row(s) affected)
>on SQL Server 2005 you can use row_number() to calculate rn
>
>|||This looks like you are creating the rows by matching the SORTED ORDER
OF THE VALUES IN EACH TABLE, in volation of the basic relational
principles. This means that the rows have no meaning whatsoever and
that you are probably doing this for display purposes, in violation of
the principle of a tiered archtecture.
However, look up a query I did to match boys and girls as dance
partners. The trick was to add a relative row in derived tables and to
use a view to close gaps when the base tables change.
CREATE VIEW DanceCard (boy_name, girl_name)
AS SELECT B.name, G.name
FROM
(SELECT B1.name, COUNT(B2.*)
FROM Boys AS B1, Boys AS B2
WHERE B2.name <= B1.name
GROUP BY B1.name) AS B(name, match_nbr)
FULL OUTER JOIN
(SELECT G1.name, COUNT(G2.*)
FROM Girls AS G1, Girls AS G2
WHERE G2.name <= G1.name
GROUP BY G1.name) AS G(name, match_nbr)
ON B.match_nbr = G.match_nbr;
This is not a good way to do such things; you really need a better
rule.|||On 5 Jan 2006 16:42:22 -0800, "--CELKO--" <jcelko212@.earthlink.net> wrote:
in <1136508142.931773.99530@.o13g2000cwo.googlegroups.com>
Is that your face in the piratesdinneradventure newspaper ads?|||>> in volation of the basic relational
principles. This means that the rows have no meaning whatsoever and
that you are probably doing this for display purposes, in violation of
the principle of a tiered archtecture. <<
In real life the problem is quite common, for instance:
- 20 non-smoking guests arrive in a hotel with 30 vacant identical
non-smoking rooms, each guest needs to get a room. And that does not
mean that "the rooms and the guests have no meaning whatsoever".
If this simple real life situation is in "volation of the basic
relational principles", as you say, that's just one more indication
that the relational theory is not perfect, it does not cover all the
bases.
Anyway, the vendors do listen to us practitioners, and they have
provided row_number() to deal with this very common problem. I guess
row_number() is in ANSI standard now, is it not?

Friday, February 24, 2012

JOIN Process Order and Performance Comparisons

Hi all,
A common SQL that I do is joining parent and child tables together (1-M
relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
number of rows.
Q1) Compare the two statements (that give the same result) below, from a
programming point of view, which one is more efficient?
Statement 1
--
SELECT *
FROM Invoice Ivo
INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
AND IvoItm.ProductType = 1 --This line is processed in WHERE.
Statement 2
--
SELECT *
FROM Invoice Ivo
INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
AND IvoItm.ProductType = 1 --This line is processed in JOIN.
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
This is something which I have been wondering for quite sometime. After
reading MSDN article "Join Fundamentals" stating, it says the JOIN statement
s
are processed first.
Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
process the JOIN?
Q3) If it does the latter 1st, would it filter out the MANY rows in IvoItm,
before doing the JOINS? Therefore improving performance, as the amount of
data to join is reduced in the IvoItm?
Q4) Using the same analogy in Q3, would there be performance gain if I
rewrite the statement using sub-query to do the filtering 1st?
SELECT *
FROM Invoice Ivo
INNER JOIN (
SELECT *
FROM InvoiceItem
WHERE ProductType = 1 --This line is processed in sub-query.
) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
Q5) And would above be efficient than using the Statement 1 and 2?Answers inline:
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:3E92F8CB-3E8A-4A4C-A0D9-71B707658D1A@.microsoft.com...
> Hi all,
> A common SQL that I do is joining parent and child tables together (1-M
> relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
> number of rows.
> Q1) Compare the two statements (that give the same result) below, from a
> programming point of view, which one is more efficient?
> Statement 1
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> AND IvoItm.ProductType = 1 --This line is processed in WHERE.
> Statement 2
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> AND IvoItm.ProductType = 1 --This line is processed in JOIN.
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
These two statements are probably going to perform equivalently, as they are
mathematically equivalent. If this was an outer join, then it will make a
difference. First thing to do is to check the plan using Query analyzer. It
should be the exact same plan.
Logically, all of the JOIN operators will be dealt with, building the set
with Invoice.* + InvoiceLineItem, eliminating rows where the join criteria
fails. Then for every row in the output you apply the where clause.
However, the optimizer can can reorganize the query to make it execute
better as long as the same results would be the same.
For the rest of your questions, try looking at the plan first. It may answer
the questions for you as the answers may be different based on the number of
rows in each table.

> This is something which I have been wondering for quite sometime. After
> reading MSDN article "Join Fundamentals" stating, it says the JOIN
> statements
> are processed first.
>
This is true logically, but it is not required if the results are the same

> Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
> this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
> process the JOIN?
> Q3) If it does the latter 1st, would it filter out the MANY rows in
> IvoItm,
> before doing the JOINS? Therefore improving performance, as the amount of
> data to join is reduced in the IvoItm?
> Q4) Using the same analogy in Q3, would there be performance gain if I
> rewrite the statement using sub-query to do the filtering 1st?
> SELECT *
> FROM Invoice Ivo
> INNER JOIN (
> SELECT *
> FROM InvoiceItem
> WHERE ProductType = 1 --This line is processed in sub-query.
> ) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> Q5) And would above be efficient than using the Statement 1 and 2?|||As noted by Louis, for Inner Joins it doesn't matter whether you specify
the predicates in the WHERE clause or in the JOIN ON clause. For Outer
Joins the meaning is different.
What the query optimizer will do, is analyse which indexes your tables
have and whether they can be s

ed or scanned in order to reduce the
I/O needed to retrieve the actual data. Then it will do an access path
analysis to see in which order the joins would be fastest. If there is
an appropriate index, then physically, the (partial) filtering will
occur before the join.
Suppose you have a clustered index on Invoice(Date). Then you will
probably see a clustered index s

on table Invoice, regardless whether
you used syntax 1 or 2. BTW: the only way to really tell is check the
query plan.
HTH,
Gert-Jan
HardKhor wrote:
> Hi all,
> A common SQL that I do is joining parent and child tables together (1-M
> relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
> number of rows.
> Q1) Compare the two statements (that give the same result) below, from a
> programming point of view, which one is more efficient?
> Statement 1
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> AND IvoItm.ProductType = 1 --This line is processed in WHERE.
> Statement 2
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> AND IvoItm.ProductType = 1 --This line is processed in JOIN.
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> This is something which I have been wondering for quite sometime. After
> reading MSDN article "Join Fundamentals" stating, it says the JOIN stateme
nts
> are processed first.
> Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
> this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
> process the JOIN?
> Q3) If it does the latter 1st, would it filter out the MANY rows in IvoItm
,
> before doing the JOINS? Therefore improving performance, as the amount of
> data to join is reduced in the IvoItm?
> Q4) Using the same analogy in Q3, would there be performance gain if I
> rewrite the statement using sub-query to do the filtering 1st?
> SELECT *
> FROM Invoice Ivo
> INNER JOIN (
> SELECT *
> FROM InvoiceItem
> WHERE ProductType = 1 --This line is processed in sub-query.
> ) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> Q5) And would above be efficient than using the Statement 1 and 2?

Wednesday, March 21, 2012

Joining two large queries as derived tables

Joining to Huge Table

Joining to Huge Table

Joining to Huge Table

Monday, March 12, 2012

Joing two tables but avoid cartesian product

Friday, February 24, 2012

JOIN Process Order and Performance Comparisons

join or subselect

Blog Archive

About Me