INNER JOIN, derived tables, etc., question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

INNER JOIN, derived tables, etc., question

Author

Topic

anna a
Starting Member

3 Posts

Posted - 2001-12-20 : 07:22:56

hello. help please, anyone. i have 3 tables: A, B, and C, each with 2 integer fields, that are related by primary and foreign key IDs:

A
A1_PK_ID
A2

B
B1_FK_ID
B2

C
C1_FK_ID
C2

i want to get all the 2nd fields that are > 100 from those records with matching key/IDs, something like:

SELECT A2, B2, C2
FROM A, B, C
WHERE A.A1_PK_ID = B.B1_FK_ID
AND A.A1_PK_ID = C.C1_FK_ID
AND A.A2 > 100
AND B.B2 > 100
AND C.C2 > 100

is that legal? or how about this?:

SELECT A2, B2, C2
FROM (
(A INNER JOIN B
ON A.A1_PK_ID = B.B1_FK_ID)
INNER JOIN C
ON A.A1_PK_ID = C.C1_FK_ID)
WHERE A2 > 100
AND B2 > 100
AND C2 > 100

or this?:

SELECT A2, B2
FROM A INNER JOIN B
ON A.A1_PK_ID = B.B1_FK_ID
AND A.A2 > 100
AND B.B2 > 100
UNION
SELECT C2
FROM A INNER JOIN C
ON A.A1_PK_ID = C.C1_FK_ID
AND A.A2 > 100
AND C.C2 > 100

or this?:

SELECT A2, B2, C2
FROM
(SELECT A2
FROM A
WHERE A.A2 > 100)
INNER JOIN
(SELECT B2
FROM B
WHERE B.B2 > 100)
ON A.A1_PK_ID = B.B1_FK_ID
INNER JOIN
(SELECT C2
FROM C
WHERE C.C2 > 100)
ON A.A1_PK_ID = C.C1_FK_ID

what's the legal and most efficient way to this?

thanx, anna a

Nazim
A custom title

1408 Posts

Posted - 2001-12-20 : 08:03:55

SELECT A2, B2, C2
FROM
(SELECT A2
FROM A
WHERE A.A2 > 100)
INNER JOIN
(SELECT B2
FROM B
WHERE B.B2 > 100)
ON A.A1_PK_ID = B.B1_FK_ID
INNER JOIN
(SELECT C2
FROM C
WHERE C.C2 > 100)
ON A.A1_PK_ID = C.C1_FK_ID

you have to give aliases for this to work

like

SELECT A2, B2, C2
FROM
(SELECT A2
FROM A
WHERE A.A2 > 100) a
INNER JOIN
(SELECT B2
FROM B
WHERE B.B2 > 100) b
ON A.A1_PK_ID = B.B1_FK_ID
INNER JOIN
(SELECT C2
FROM C
WHERE C.C2 > 100) c
ON A.A1_PK_ID = C.C1_FK_ID

this should work faster then the others. its just my thought i havent cross checked it. but you can use query analyzer and check its execution plan by using Show Execution Plan.

HTH

-------------------------
"Success is when Preparedness meets Opportunity"

anna a
Starting Member

3 Posts

Posted - 2001-12-20 : 16:14:24

thanx, Nimaz. that makes sense: the derived table is always <= the original table, so if the derived tables < original tables, then joining them should be faster.

btw, since SQL is case-insensitive, if the original tables are:

A, B, C

should i name the derived tables aliases something like:

aa, bb, cc

does the SQL engine care?

also, should i use the aliases (or the originals) for the ON clause?:

SELECT aa.A2, bb.B2, cc.C2
FROM
(SELECT A2
FROM A
WHERE A2 > 100) aa
INNER JOIN
(SELECT B2
FROM B
WHERE B2 > 100) bb
ON aa.A1_PK_ID = bb.B1_FK_ID
INNER JOIN
(SELECT C2
FROM C
WHERE C2 > 100) cc
ON aa.A1_PK_ID = cc.C1_FK_ID

thanx, anna a

Nazim
A custom title

1408 Posts

Posted - 2001-12-21 : 23:32:10

oops! you got my name wrong its Nazim not Nizam

quote:

thanx, Nimaz. that makes sense: the derived table is always <= the original table, so if the derived tables < original tables, then joining them should be faster.

Yeah, whenever you give a Alias name you got to stick to it, you cant use the original name of the table, but you can have a alias name same as the tablename(though that will look foolish to have it same, just for heck of clarifying)

quote:

btw, since SQL is case-insensitive, if the original tables are:

A, B, C

should i name the derived tables aliases something like:

aa, bb, cc

does the SQL engine care?

yeah whenever you use join clause it becomes necessary to use on clause otherwise Sql reports a error

quote:

also, should i use the aliases (or the originals) for the ON clause?:

SELECT aa.A2, bb.B2, cc.C2
FROM
(SELECT A2
FROM A
WHERE A2 > 100) aa
INNER JOIN
(SELECT B2
FROM B
WHERE B2 > 100) bb
ON aa.A1_PK_ID = bb.B1_FK_ID
INNER JOIN
(SELECT C2
FROM C
WHERE C2 > 100) cc
ON aa.A1_PK_ID = cc.C1_FK_ID

thanx, anna a

-------------------------
"Graz's Baby is my Master:)"

anna a
Starting Member

3 Posts

Posted - 2001-12-23 : 11:48:57

quote:
oops! you got my name wrong its Nazim not Nizam :(

so sorry, Nazim -- it was late in the day. :) thanx for the clarification.

anna a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources