Re: Difference in Query Result Using Left Outer Join
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 09/21/04
- Next message: Tibor Karaszi: "Re: Difference in Query Result Using Left Outer Join"
- Previous message: Gert E.R. Drapers: "Re: Stack?"
- In reply to: rgn: "RE: Difference in Query Result Using Left Outer Join"
- Next in thread: Zach Wells: "Re: Difference in Query Result Using Left Outer Join"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 21 Sep 2004 14:19:51 +0200
> But, going by your mail, looks like the WHERE behaves like the SET ROWCOUNT
> in that it restricts the records after the Join is performed.
SET ROWCOUNT specifies a "physical" limit. After the number of rows, SQL Server stops processing
your query.
The WHERE clause is a *logical* restriction. "Only return the rows which satisfies this condition."
But you are correct in the sense that the FROM clause (JOIN belongs to the FROM clause) is logically
processed before the WHERE clause.
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "rgn" <rgn@discussions.microsoft.com> wrote in message news:B344CDC9-7D6E-448D-9B02-F57DD3F0E282@microsoft.com... > I was under the impression that the WHERE in SQL-92 stnadards is used to > fetch only those records that satisfy the Where Criteria and then this set > is used to Join with the other table. > > ie, WHERE T2.TYPE IN ('HOME', 'WORK') is used to fetch all the records > that have a Type of 'HOME' & 'WORK' and this result set is used to Join with > the table TAB1 T1. > > But, going by your mail, looks like the WHERE behaves like the SET ROWCOUNT > in that it restricts the records after the Join is performed. > > rgn > "Mike Epprecht (SQL MVP)" wrote: > > > Hi > > > > A WHERE clause gets applied after the join, whilst the AND gets applied > > during the join. Think of the WHERE clause as an afterthought, it gets > > applied to the set after all join operations have been performed. > > > > Only with LEFT OUTER and RIGHT OUTER joins do you see this behaviour. With > > INNER JOINS, you get exactly the same result when using a AND or WHERE. > > > > If you were to run the 2 queries in Query Analyzer with Display Execution > > Plan switched on, wou will see how the 2 queries perform differently. > > > > Regards > > Mike > > > > "rgn" wrote: > > > > > Hello All, > > > > > > Would anyone know why this difference in the results. Here is the Table > > > Creation > > > Script : > > > CREATE TABLE [dbo].[tab1] ( > > > [id] [int] NULL > > > ) ON [PRIMARY] > > > GO > > > > > > id > > > ----------- > > > 1 > > > 2 > > > 3 > > > 4 > > > > > > (4 row(s) affected) > > > > > > CREATE TABLE [dbo].[tab2] ( > > > [id] [int] NULL , > > > [type] [varchar] (10) NULL > > > ) ON [PRIMARY] > > > GO > > > > > > id type > > > ----------- ---------- > > > 1 home > > > 2 work > > > 1 work > > > 3 adfadf > > > 3 adfadf > > > > > > (5 row(s) affected) > > > > > > The Following Query Returns the result mentioned below : > > > SELECT T1.ID, T2.Type > > > FROM TAB1 T1 > > > LEFT OUTER JOIN TAB2 T2 > > > ON T1.ID = T2.ID > > > AND T2.TYPE IN ('HOME', 'WORK') > > > > > > ID Type > > > ----------- ---------- > > > 1 home > > > 1 work > > > 2 work > > > 3 NULL > > > 4 NULL > > > > > > (5 row(s) affected) > > > > > > The following slightly re-written by including a Where Clause returns the > > > result listed below: > > > SELECT T1.ID, T2.Type > > > FROM TAB1 T1 > > > LEFT OUTER JOIN TAB2 T2 > > > ON T1.ID = T2.ID > > > WHERE T2.TYPE IN ('HOME', 'WORK') > > > > > > ID Type > > > ----------- ---------- > > > 1 home > > > 1 work > > > 2 work > > > > > > (3 row(s) affected) > > > > > > > > > The second Result ignores the Column for which tab2 does not have records. > > > I was expecting the result of this Query to be the same as the result of the > > > first query. > > > > > > But the result of the second query is equivalent to the result of an Inner > > > Join. > > > > > > Would anyone have an insight into the behaviour ? > > > > > > Thanks, > > > rgn
- Next message: Tibor Karaszi: "Re: Difference in Query Result Using Left Outer Join"
- Previous message: Gert E.R. Drapers: "Re: Stack?"
- In reply to: rgn: "RE: Difference in Query Result Using Left Outer Join"
- Next in thread: Zach Wells: "Re: Difference in Query Result Using Left Outer Join"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
