JOINS to Sub-Queries -vs- JOINS to Tables

SQL Server 2000

Howdy All.

Is it going to be faster to join several tables together and then
select what I need from the set or is it more efficient to select only
those columns I need in each of the tables and then join them together
?

The joins are all Integer primary keys and the tables are all about the
same.

I need the fastest most efficient method to extract the data as this
query is one of the most used in the system.

Thanks,

CraigOn 11 Aug 2005 09:24:08 -0700, csomberg@.dwr.com wrote:

>SQL Server 2000
>Howdy All.
>Is it going to be faster to join several tables together and then
>select what I need from the set or is it more efficient to select only
>those columns I need in each of the tables and then join them together
>?
>The joins are all Integer primary keys and the tables are all about the
>same.
>I need the fastest most efficient method to extract the data as this
>query is one of the most used in the system.
>Thanks,
>Craig

Hi Craig,

I'm not sure I understand your question. Are you asking about the
performance difference between queries like these two?

SELECT A.something, B.otherthing
FROM TableA AS A
INNER JOIN TableB AS B
ON A.xxx = B.xxx
WHERE A.yyy = y
AND B.zzz = z

or

SELECT A.something, B.otherthing
FROM (SELECT xxx, something
FROM TableA
WHERE A.yyy = y) AS A
INNER JOIN (SELECT xxx, otherthing
FROM TableB
WHERE B.zzz = z) AS B
ON A.xxx = B.xxx

My first guess is that there will be no difference. The optimizer is
free to rearrange the query every way it wants, as long as the end
results are the same. They will probably result in the same execution
plan.

On the other hand, it is very hard to predict what the optimizer will
do. It often does a good job, but there still are situations where it
shows that it's just a program.

If you really want to be sure, then why don't you simply test both
against your system?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(csomberg@.dwr.com) writes:
> Is it going to be faster to join several tables together and then
> select what I need from the set or is it more efficient to select only
> those columns I need in each of the tables and then join them together
> ?
> The joins are all Integer primary keys and the tables are all about the
> same.
> I need the fastest most efficient method to extract the data as this
> query is one of the most used in the system.

Your query is open to several interpretations, so the answers you get
may not address your real issue.

If your idea is to first join two tables, get those columns into
a temp table, join that with the next table, then this is generally
not a good idea. Although, when it comes to performance there a few
definitive answers. For a certain query, this could actually be a
good strategy. But as a general approach, it's better to throw in
all tables into one query.

And you should not use SELECT * - only list the columns you actually
need.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I need the fastest most efficient method to extract the data as this query is one of the most used in the system. <<

Then test them. But my guess is that the optimizer will do them the
same way. Putting all the tables in the FROM clause will be much easier
to read and maintain, however.sql

joins

hi dis is kalaivanan,
which one of inner join or left join is efficient and in what way.Hi,
Inner join is always the best; but the purpose of Left and Right outer joins
serves different purpose. If you need all the data immeterial of join
from left most table participate in the join then use LEFT OUTER and if it
is vice versa use RIGHT OUTER join. INNER JOIN gives
only the matching records of the join.
Read the below infor from books online:-
Join conditions can be specified in either the FROM or WHERE clauses;
specifying them in the FROM clause is recommended. WHERE and HAVING clauses
can also contain search conditions to further filter the rows selected by
the join conditions.
Joins can be categorized as:
a.. Inner joins (the typical join operation, which uses some comparison
operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based
on the values in common columns from each table. For example, retrieving all
rows where the student identification number is the same in both the
students and courses tables.
b.. Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when
they are specified in the FROM clause:
a.. LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left
table specified in the LEFT OUTER clause, not just the ones in which the
joined columns match. When a row in the left table has no matching rows in
the right table, the associated result set row contains null values for all
select list columns coming from the right table.
b.. RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from
the right table are returned. Null values are returned for the left table
any time a right table row has no matching row in the left table.
c.. FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables.
Any time a row has no match in the other table, the select list columns from
the other table contain null values. When there is a match between the
tables, the entire result set row contains data values from the base tables.
c.. Cross joins.
Cross joins return all rows from the left table, each row from the left
table is combined with all rows from the right table. Cross joins are also
called Cartesian products.
Thanks
Hari
SQL Server MVP
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1156571058.708245.325900@.m79g2000cwm.googlegroups.com...
> hi dis is kalaivanan,
> which one of inner join or left join is efficient and in what way.
>

joins

hi dis is kalaivanan,
which one of inner join or left join is efficient and in what way.Hi,
Inner join is always the best; but the purpose of Left and Right outer joins
serves different purpose. If you need all the data immeterial of join
from left most table participate in the join then use LEFT OUTER and if it
is vice versa use RIGHT OUTER join. INNER JOIN gives
only the matching records of the join.
Read the below infor from books online:-
Join conditions can be specified in either the FROM or WHERE clauses;
specifying them in the FROM clause is recommended. WHERE and HAVING clauses
can also contain search conditions to further filter the rows selected by
the join conditions.
Joins can be categorized as:
a.. Inner joins (the typical join operation, which uses some comparison
operator like = or <> ). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based
on the values in common columns from each table. For example, retrieving all
rows where the student identification number is the same in both the
students and courses tables.
b.. Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when
they are specified in the FROM clause:
a.. LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left
table specified in the LEFT OUTER clause, not just the ones in which the
joined columns match. When a row in the left table has no matching rows in
the right table, the associated result set row contains null values for all
select list columns coming from the right table.
b.. RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from
the right table are returned. Null values are returned for the left table
any time a right table row has no matching row in the left table.
c.. FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables.
Any time a row has no match in the other table, the select list columns from
the other table contain null values. When there is a match between the
tables, the entire result set row contains data values from the base tables.
c.. Cross joins.
Cross joins return all rows from the left table, each row from the left
table is combined with all rows from the right table. Cross joins are also
called Cartesian products.
Thanks
Hari
SQL Server MVP
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1156571058.708245.325900@.m79g2000cwm.googlegroups.com...
> hi dis is kalaivanan,
> which one of inner join or left join is efficient and in what way.
>

joins

hi dis is kalaivanan,
which one of inner join or left join is efficient and in what way.kalaivanan wrote:

Quote:

Originally Posted by

hi dis is kalaivanan,
which one of inner join or left join is efficient and in what way.

Inner joins and outer joins achieve different results so in general it
won't make sense to compare them on performance.

In cases where an inner and outer join are guaranteed to give the same
result I suggest you test it out for yourself. Performance is too
heavily dependent on other factors for there to a be a single, simple
answer.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:

Quote:

Originally Posted by

In cases where an inner and outer join are guaranteed to give the same
result I suggest you test it out for yourself.

And in that case, I expect the optimizer to see that the left join is
really an inner join, and thus you would get the same query plan.

The question is simply not meaningful, since, as David pointed, the
difference lies in functionality.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

joining table on on last entered record

Dear All,

What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B.

I'd like to select every record in table A but only joining the last relevant record from table B. So:

Table A:

A1 Prj1
A2 Prj2

Table B:

B1 A1 23/12/2005
B2 A1 26/12/2005
B3 A1 2/1/2007
B4 A2 25/12/2006
B5 A2 1/1/2007

So I'd like to list using the most efficient way this:

A1 Prj1 B3 2/1/2007
A2 Prj2 B5 1/1/2007

I'm assuming this is NOT the most efficient way:

select A, (select top 1 date from B orderBy ...)

Any suggestions?Maybe this:

select A, B
from A
inner join A on A.Aid = B.Aid
where B.date = (select max(B.date) from B where...) ...

this works faster but is there a better way? (I'm sure there is)

Anyone?|||I use this:
select A,
B
from A
inner join --LastRecords
select A,
max(date) as date
from A
group by A) LastRecords
on A.A = LastRecords.A
and A.date = LastRecords.date
...but I can't promise that it is faster.|||Another possibility is:
SELECT A.aid
,A.prj
,MAX(B.DATE)
FROM A
INNER JOIN B ON B.aid = A.aid
GROUP BY A.aid, A.prj
Don't know how this will perform but as long as you have the right index (I'd recomment one on "B.Aid, B.date") I don't think it will differ much between the various methods.|||Thanks for that, how about when the record in table A have no records in table B yet, but I'd still like to list it, but with a NULL value in the columns from table B?|||use a LEFT OUTER JOIN instead of INNER JOIN|||Thanks for that

Join to select a 'weighted' column

Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.

declare @.inv table (item varchar(50), supplierid int)

declare @.supplier table (supplierid int, weight int)

set nocount on

insert into @.inv values ('item1', 1)

insert into @.inv values ('item1', 2)

insert into @.inv values ('item2', 2)

insert into @.inv values ('item2', 3)

insert into @.supplier values(1, 30)

insert into @.supplier values(2, 20)

insert into @.supplier values(3, 10)

-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3

select item, ps2.supplierid from @.supplier ps2 join

(select item, min(ps.weight)'weight'

from @.inv inv join @.supplier ps on inv.supplierid=ps.supplierid

group by item) iw on ps2.weight=iw.weight

Is there a better alternative to this?

Thanks in advance,

Mike

You can do the following in SQL Server 2005:

select item, supplierid
from (
select i.item, s.supplierid, row_number() over(partition by i.item order by s.weight) as wt
from @.supplier as s
join @.inv as i
on i.supplierid = s.supplierid
) as si
where wt = 1

But the most efficient way is to do below:

select item, cast(substring(wt, 5, 4) as int) as supplierid
from (
select i.item, min(cast(s.weight as binary(4)) + cast(s.supplierid as binary(4))) as wt
from @.supplier as s
join @.inv as i
on i.supplierid = s.supplierid
group by i.item
) as si

The second method will work only if the weight/supplierid values are greater than or equal to zero due to the conversion to binary. You can make it work for negative values also by modifying the expression. The trick is to get a sortable value using a combination of weight/supplierid that you can apply the aggregate function on and then get the individual values out.

|||

Thank you very much,

I will look at both.

Mike