May 23, 2012 at 11:53 pm
here i am having 2 table
create table Table1
(
ID int identity(1,1),name1 varchar(100)
)
insert into Table1
select 'cricket' union all
select 'football'
GO
create table Table2
(
ID int identity(1,1),name2 varchar(100)
)
insert into Table2
select 'bowling' union all
select 'batting' union all
select 'NULL' union all
select 'NULL' union all
select 'NULL'
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
/* RIGHT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
GO
/* OUTER JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL
GO
DROP TABLE table1
DROP TABLE table2
i am getting output like this
ID name1 ID name2
NULL NULL 3 NULL
NULL NULL 4 NULL
NULL NULL 5 NULL
i want to get the same output of only table2 id should be presented but they told me
dont use
condition
dont use in (where t2.id in null)
1.dont use not in also
2.dont use <>
3.don't acess with 'null' text
4.dont use order by desc with top 5 in tabe 2
whether it is possible to get these result
ID name1 ID name2
NULL NULL 3 NULL
NULL NULL 4 NULL
NULL NULL 5 NULL
i am getting the same result
OUTER JOIN,and right OUTER JOIN - WHERE NULL
but i used null in where condition
so
plz tell me whether it is possible to get these result
May 24, 2012 at 12:11 am
SELECT *
FROM dbo.Table1 AS t1
RIGHT OUTER JOIN dbo.Table2 AS t2 ON
t2.ID = t1.ID
EXCEPT
SELECT *
FROM dbo.Table1 AS t1
INNER JOIN dbo.Table2 AS t2 ON
t2.ID = t1.ID;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 24, 2012 at 12:28 am
hi SQL Kiwi
thanks a lot i didn't
use except still now thanks a lot for ur job
thanks
May 24, 2012 at 12:51 am
You don't have to use EXCEPT, there are many ways to do this:
SELECT *
FROM dbo.Table1 AS t1
RIGHT OUTER JOIN dbo.Table2 AS t2 ON
t2.ID = t1.ID
WHERE NOT EXISTS
(
SELECT *
FROM dbo.Table1 AS t3
WHERE t3.ID = t2.ID
);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply