Performing join problem - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Performing join problem

Author

Topic

john20
Starting Member

30 Posts

Posted - 2012-08-14 : 08:32:52

Hi All,

I have found this 'Join' query example in one of the univeristy's documents but not able to find any practical example of that.

Could someone please help me to write the practical example of the below example:

( A WHERE restriction-on-A ) JOIN ( B WHERE restriction-on-B )

Explanation is given below:

Doing the restrictions before the join, as the transformed
version of the expression requires, is generally a good idea,
because it has the twin effects of:

(a) reducing the size of the input to the join, and hence
reducing the amount of data to be scanned in performing that
join, and

(b) reducing the size of the output from the join as well, which
could make the difference between being able to keep that output
in main memory and having to spool it out on to the disk.

Could you please tell me how can I write the query using this example.

Many Thanks
John

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-14 : 09:02:21

Looks like the text isn't very clear here. There are a number of ways of doing it but I think you are probably wanting derived tables....

*(this is sql server specific -- may work on other rdbms but I'm not guaranteeing it)


SELECT t1.[key], t1.[column], t2.[column]
FROM
    (
    SELECT [key], [column] FROM Table1 WHERE [foo] = 'bar'
    )
    AS t1

    JOIN (
        SELECT [key], [column] FROM Table2 WHERE [woo] = 'coo'
        )
        AS t2 ON t2.[key] = t1.[key]
WHERE
    <Further Predicate list here>

Note the alias (t1, t2) for the derived tables....

This would be exactly the same as making a VIEW over the tables and then JOINING the two views together.

ALL relation database products I can think of will actually parse this SQL into their own execution plans and may do something different.

I'd bet that SQL Server will treat the above code identically to


SELECT t1.[key], t1.[column], t2.[column]
FROM
    Table1 AS t1
    JOIN Table2 AS t2 ON t2.[Key] = t1.[Key]
WHERE
    t1.[foo] = 'bar'
    AND t2.[woo] = 'coo'

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

john20
Starting Member

30 Posts

Posted - 2012-08-14 : 09:26:28

Hi Charlie,

Thank you very much for you help and answer.

I agree with you that Sql server will

treat the above code identically to

SELECT t1.[key], t1.[column], t2.[column]
FROM
Table1 AS t1
JOIN Table2 AS t2 ON t2.[Key] = t1.[Key]
WHERE
t1.[foo] = 'bar'
AND t2.[woo] = 'coo'

I have tried both the queries and there is no difference in execution time.

so I am staying with the above solution, because my application support both SQL and Oracle databases.

Many thanks again.
John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources