 |
|
 |
|
Next: Why does the Cartesian product of two tables diff..
|
| Author |
Message |
External

Since: Jun 18, 2008 Posts: 2
|
(Msg. 1) Posted: Wed Jun 18, 2008 3:42 am
Post subject: What happens when a "NULL" condition is applied to the Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
Hi, I have below two tables "TAB1" & "TAB2":
TAB1
----
T1C1 T1C2
---- ----
1 A
2 B
3 C
TAB2
----
T2C1 T2C2
---- ----
2 D
3 E
4 F
When I executed the below sql statement, it displayed the Cartesian product of the rows of both the tables, which I understood.
SELECT t1.t1c1, t2.t2c1
FROM TAB1 t1
LEFT JOIN TAB2 t2 ON t1.t1c1 IS NOT NULL;
But, when I execute the below sql statement, its displaying the below output, which I am not able to understand.
SELECT t1.t1c1, t2.t2c1
FROM TAB1 t1
LEFT JOIN TAB2 t2 ON t1.t1c1 IS NULL;
Output:
------
T1C1 T2C1
---- ----
1 null
2 null
3 null
Can anyone please explain me why its displaying the above output. It shouldn't have displayed any rows.
Thanks in Advance... >> Stay informed about: What happens when a ""NULL"" condition is applied to the |
|
| Back to top |
|
 |  |
External

Since: Jun 18, 2008 Posts: 11
|
(Msg. 2) Posted: Wed Jun 18, 2008 6:02 am
Post subject: Re: What happens when a "NULL" condition is applied to the Cartesian [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
 |  |
External

Since: Jan 10, 2008 Posts: 640
|
(Msg. 3) Posted: Wed Jun 18, 2008 6:21 am
Post subject: Re: What happens when a "NULL" condition is applied to the Cartesian [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
|
|
|
On Jun 18, 5:42 am, Vishal Bongale wrote:
> Hi, I have below two tables "TAB1" & "TAB2":
>
> TAB1
> ----
> T1C1 T1C2
> ---- ----
> 1 A
> 2 B
> 3 C
>
> TAB2
> ----
> T2C1 T2C2
> ---- ----
> 2 D
> 3 E
> 4 F
>
> When I executed the below sql statement, it displayed the Cartesian product of the rows of both the tables, which I understood.
> SELECT t1.t1c1, t2.t2c1
> FROM TAB1 t1
> LEFT JOIN TAB2 t2 ON t1.t1c1 IS NOT NULL;
>
> But, when I execute the below sql statement, its displaying the below output, which I am not able to understand.
> SELECT t1.t1c1, t2.t2c1
> FROM TAB1 t1
> LEFT JOIN TAB2 t2 ON t1.t1c1 IS NULL;
>
> Output:
> ------
> T1C1 T2C1
> ---- ----
> 1 null
> 2 null
> 3 null
>
> Can anyone please explain me why its displaying the above output. It shouldn't have displayed any rows.
> Thanks in Advance...
if you want a Cartesian product, use CROSS JOIN. >> Stay informed about: What happens when a ""NULL"" condition is applied to the |
|
| Back to top |
|
 |  |
External

Since: Nov 11, 2003 Posts: 854
|
(Msg. 4) Posted: Wed Jun 18, 2008 7:40 pm
Post subject: Re: What happens when a "NULL" condition is applied to the Cartesian product? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
|
|
|
<Vishal Bongale> wrote in message ...
> Hi, I have below two tables "TAB1" & "TAB2":
>
> TAB1
> ----
> T1C1 T1C2
> ---- ----
> 1 A
> 2 B
> 3 C
>
> TAB2
> ----
> T2C1 T2C2
> ---- ----
> 2 D
> 3 E
> 4 F
>
> When I executed the below sql statement, it displayed the Cartesian
> product of the rows of both the tables, which I understood.
> SELECT t1.t1c1, t2.t2c1
> FROM TAB1 t1
> LEFT JOIN TAB2 t2 ON t1.t1c1 IS NOT NULL;
>
> But, when I execute the below sql statement, its displaying the below
> output, which I am not able to understand.
> SELECT t1.t1c1, t2.t2c1
> FROM TAB1 t1
> LEFT JOIN TAB2 t2 ON t1.t1c1 IS NULL;
>
> Output:
> ------
> T1C1 T2C1
> ---- ----
> 1 null
> 2 null
> 3 null
>
> Can anyone please explain me why its displaying the above output. It
> shouldn't have displayed any rows.
> Thanks in Advance...
A LEFT JOIN always returns at least as many rows as in the preserved table
(the table on the "left" side of the join) unless you further restrict the
result with a WHERE clause.
--
David Portas >> Stay informed about: What happens when a ""NULL"" condition is applied to the |
|
| Back to top |
|
 |  |
| Related Topics: | "IS NULL" check applied to a LOB field causes "lob logical.. - It looks like an "IS NULL" predicate, when applied to a LOB field, causes that LOB's pages to be read. This results in a very slow processing of queries like: SELECT (case when [myColumn] is not null then 1 else 0 end) as test1 FROM myTable ...
can pivot be applied to group by ? - For this table Id bigint identity(1,1) Attribute nvarchar(50) not null PeriodNumber bigint not null DataDateTime DateTime not null Value decimal(18,5) not null I need to retrieve each period as a row with its columns as a set of attributes' values. ...
error adding user when audit trigger applied - Hi I have a trigger that will audit all ddl events. When adding a user through SSMS and give the user permission to certain databases only not all databases I get the error below. If I grant access using the code below CREATE USER [Titan\john.dow] FOR...
view seems to ignore the 'concat null yields null' option - Hello all I have a database and am concatenatiing strings in a view. the value is coming up as null so I assumed that this was becasue 'concat null yields null' was set to true. I changed this setting (using the below) and checked it with the query o...
testing for null and not null in case statement - I want to know whether a field has a date or not, and then group by that result in a query. I'm trying: case activate_date when null then 'active next bill date' when not null then 'active' end and it didn't like that. Didn't like that "not"... |
|
You can post new topics in this forum You can reply to topics in this forum You can edit your posts in this forum You can delete your posts in this forum You can vote in polls in this forum
|
|
|
|
 |
|
|