February 14, 2008 at 4:12 am
Hi, If you use ANSI joins in a select statement, are there any occasions when you would still need to use a WHERE clause?
Thanks.
February 14, 2008 at 5:04 am
Alan G (2/14/2008)
Hi, If you use ANSI joins in a select statement, are there any occasions when you would still need to use a WHERE clause?Thanks.
It is easier to show this via an example:
create table t1 (a int, b int)
create table t2 (b int, c int)
insert into t1 values(1,1)
insert into t1 values(1,2)
insert into t1 values(1,3)
insert into t2 values(1,2)
insert into t2 values(1,null)
insert into t2 values(2,2)
SELECT *
FROM t1
LEFT JOIN t2 ON t1.b = t2.b
AND c IS NOT NULL
SELECT *
FROM t1
LEFT JOIN t2 ON t1.b = t2.b
WHERE c IS NOT NULL
Regards,
Andras
February 14, 2008 at 5:56 am
Thanks Andras for providing the example. Having run it, I needed to ponder on it for a bit, but I think I get it now - the difference is quite subtle! Thanks again.
February 14, 2008 at 7:11 am
You should note that if you alter the left join to an inner join both queries return the same result. The reason the inner join works is the inner join is matching column bs and has a stipulation that column c cannot be null. The reason the left join failed is becase a left join will return all records from the left table regardless of a match, in the right table, which would still return the unwanted record. The where clause IS the mechanism that is supposed to filter the data, not the join. The ansi join's job is to relate the tables to one another and thats pretty much it.
For more consistant/accurate results, I would rely on the where clause to filter out unwanted data.
February 14, 2008 at 7:15 am
The thing to remember is that logically, they are different operations. They occur at different points in the query processing and will affect data in different ways. The JOIN criteria should, in most cases, always only be the JOIN criteria (there are cool & wonderful exceptions, but I'd suggest only going there when you're sure) and the WHERE clause is for filtering the result set (there are other cool & wonderful exceptions here too, again, targeting exceptions as an exceptional operation is safest).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 14, 2008 at 2:39 pm
create table t1 (a int, b int)
create table t2 (b int, c int)
insert into t1 values(1,1)
insert into t1 values(1,2)
insert into t1 values(1,3)
insert into t2 values(1,2)
insert into t2 values(1,null)
insert into t2 values(2,2)
Here's a question for the experts
Using Andras' example what is the difference between
SELECT *
FROM t1
LEFT JOIN t2 ON t1.b = t2.b
AND c IS NOT NULL
and
SELECT *
FROM t1
LEFT JOIN (SELECT * FROM t2 WHERE c IS NOT NULL) t2
ON t1.b = t2.b
I belive the output will always be the same but what about the performance.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 15, 2008 at 1:51 am
Performance here will be the same (but you have tiny tables, no indexes, ...) In both cases there will be a full table scan, ...
If you are interested in exploring the differences, then have a look at the actual execution plans (in Management Studio: Query->Include Actual Execution Plan), and execute the queries. There will be an extra tab in your results. You can then look at the various steps SQL Server has performed while executing the query. You can get more information on these steps by hovering over them, or looking at their properties (right click, properties). Whether an step is expensive, should be avoided, you will be able to read up on Books Online or the Web.
Regards,
Andras
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply