Welcome to dbFreaks.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

What happens when a "NULL" condition is applied to the

 
   Database Help (Home) -> Programming RSS
Next:  Why does the Cartesian product of two tables diff..  
Author Message
Vishal Bongale

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
Login to vote
Moby

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?)

take a look in the help
http://msdn.microsoft.com/en-us/library/aa178429(SQL.80).aspx

and it's written: "It ALSO returns..."

try out and you'll probably see
SELECT t1.t1c1, t2.t2c1
FROM TAB1 t1
LEFT JOIN TAB2 t2 on t1.t1c1 ='1';

kr moby

 >> Stay informed about: What happens when a ""NULL"" condition is applied to the 
Back to top
Login to vote
Alex Kuznetsov

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
Login to vote
David Portas

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
Login to vote
Display posts from previous:   
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&quot...
   Database Help (Home) -> Programming All times are: Pacific Time (US & Canada)
Page 1 of 1

 
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



[ Contact us | Terms of Service/Privacy Policy ]