SELECT
-
I Have 4 table and I need to find out the list of the rows that are present in Table A and doesn’t exist in either table B or Table C or table D.
Shas3
-
Assuming you have the same Primary Keys on all 4 tables, you can use the following to get rows in A that dont exist in either of B, C or D.
select <column_list> from TableA a where
not exists (select 1 from TableB b where b.PK = a.PK)
and not exists (select 1 from TableC c where c.PK = a.PK)
and not exists (select 1 from TableD d where d.PK = a.PK)
-
You can also do the following. Substitue your column name instead of id.
select id from tablea where id not in(select id from tableb union select id from tablec union select id from tabled)
-
No, don't do that...use left joins instead for performance reasons.
Select a.*
From TableA a
LEFT JOIN TableB b on a.PK = b.PK
LEFT JOIN TableC c on a.PK = c.PK
LEFT JOIN TableD d on a.PK = d.PK
where b.PK is null
or c.PK is null
or d.PK is null
Signature is NULL
-
You can also do the following
Select a.*
From TableA a
LEFT JOIN TableB b on a.PK = b.PK
LEFT JOIN TableC c on a.PK = c.PK
LEFT JOIN TableD d on a.PK = d.PK
where b.PK is null
and c.PK is null
and d.PK is null
-
I agree. my bad.
-
"I Have 4 table and I need to find out the list of the rows that are present in Table A and doesn’t exist in either table B or Table C or table D." Shas3
Dont' use "and" in the where clause, use "or".
cl
Signature is NULL
-
Could you post the explanation of why left Joins is better than UNION?
Thanks
Always learning,
Steve
-
I created the following script
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLEA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLEA]
GO
CREATE TABLE [dbo].[TABLEA] (
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TABLEA] WITH NOCHECK ADD
CONSTRAINT [PK_TABLEA] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]GO
INSERT INTO [dbo].[TABLEA]([ID])
VALUES(1)
GO
INSERT INTO [dbo].[TABLEA]([ID])
VALUES(2)
GO
INSERT INTO [dbo].[TABLEA]([ID])
VALUES(3)
GO
INSERT INTO [dbo].[TABLEA]([ID])
VALUES(4)
GO
INSERT INTO [dbo].[TABLEA]([ID])
VALUES(5)
GO
INSERT INTO [dbo].[TABLEA]([ID])
VALUES(7)
GO
INSERT INTO [dbo].[TABLEA]([ID])
VALUES(8)
GO
SELECT [ID] FROM [dbo].[TABLEA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLEB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLEB]
GO
CREATE TABLE [dbo].[TABLEB] (
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TABLEB] WITH NOCHECK ADD
CONSTRAINT [PK_TABLEB] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]GO
INSERT INTO [dbo].[TABLEB]([ID])
VALUES(10)
GO
INSERT INTO [dbo].[TABLEB]([ID])
VALUES(12)
GO
INSERT INTO [dbo].[TABLEB]([ID])
VALUES(13)
GO
INSERT INTO [dbo].[TABLEB]([ID])
VALUES(14)
GO
INSERT INTO [dbo].[TABLEB]([ID])
VALUES(5)
GO
INSERT INTO [dbo].[TABLEB]([ID])
VALUES(17)
GO
INSERT INTO [dbo].[TABLEB]([ID])
VALUES(18)
GO
SELECT [ID] FROM [dbo].[TABLEB]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLEC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLEC]
GO
CREATE TABLE [dbo].[TABLEC] (
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TABLEC] WITH NOCHECK ADD
CONSTRAINT [PK_TABLEC] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]GO
INSERT INTO [dbo].[TABLEC]([ID])
VALUES(20)
GO
INSERT INTO [dbo].[TABLEC]([ID])
VALUES(21)
GO
INSERT INTO [dbo].[TABLEC]([ID])
VALUES(23)
GO
INSERT INTO [dbo].[TABLEC]([ID])
VALUES(4)
GO
INSERT INTO [dbo].[TABLEC]([ID])
VALUES(25)
GO
INSERT INTO [dbo].[TABLEC]([ID])
VALUES(27)
GO
INSERT INTO [dbo].[TABLEC]([ID])
VALUES(28)
GO
SELECT [ID] FROM [dbo].[TABLEC]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLED]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLED]
GO
CREATE TABLE [dbo].[TABLED] (
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TABLED] WITH NOCHECK ADD
CONSTRAINT [PK_TABLED] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]GO
INSERT INTO [dbo].[TABLED]([ID])
VALUES(1)
GO
INSERT INTO [dbo].[TABLED]([ID])
VALUES(31)
GO
INSERT INTO [dbo].[TABLED]([ID])
VALUES(33)
GO
INSERT INTO [dbo].[TABLED]([ID])
VALUES(34)
GO
INSERT INTO [dbo].[TABLED]([ID])
VALUES(35)
GO
INSERT INTO [dbo].[TABLED]([ID])
VALUES(37)
GO
INSERT INTO [dbo].[TABLED]([ID])
VALUES(38)
GO
SELECT [ID] FROM [dbo].[TABLED]
GO
SET STATISTICS IO ON
go
Select a.[ID], b.[ID], c.[ID], d.[ID]
From TableA a
LEFT JOIN TableB b on a.[ID] = b.[ID]
LEFT JOIN TableC c on a.[ID] = c.[ID]
LEFT JOIN TableD d on a.[ID] = d.[ID]
where b.[ID] is null
AND c.[ID] is null
AND d.[ID] is null
GO
select [ID] from tablea where [ID] not in(select [ID] from tableb union select [ID] from tablec union select [ID] from tabled)
GO
SET STATISTICS IO OFF
go
The results from the last two queries are the same
2 NULL NULL NULL
3 NULL NULL NULL
7 NULL NULL NULL
8 NULL NULL NULL
AND
2
3
7
8
The Statistics on the the two queries are the same
Table 'TABLED'. Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'TABLEC'. Scan count 6, logical reads 12, physical reads 0, read-ahead reads 0.
Table 'TABLEB'. Scan count 7, logical reads 14, physical reads 0, read-ahead reads 0.
Table 'TABLEA'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
AND
Table 'TABLED'. Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0.
Table 'TABLEC'. Scan count 6, logical reads 12, physical reads 0, read-ahead reads 0.
Table 'TABLEB'. Scan count 7, logical reads 14, physical reads 0, read-ahead reads 0.
Table 'TABLEA'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
I renew the question... Why is Left Join prefered over UNION if the statistics are the same. Perhaps I have too small a sample. I will give the tables more rows.
Thoughts comments?
Steve
-
OK I changed my script and received these results, which I think indicate that the UNION has less Logical reads. HMMM?
Always learning...
Steve
Select a.[ID], b.[ID], c.[ID], d.[ID]
From TableA a
LEFT JOIN TableB b on a.[ID] = b.[ID]
LEFT JOIN TableC c on a.[ID] = c.[ID]
LEFT JOIN TableD d on a.[ID] = d.[ID]
where b.[ID] is null
AND c.[ID] is null
AND d.[ID] is null
GO
Table 'TABLED'. Scan count 499, logical reads 998, physical reads 0, read-ahead reads 0.
Table 'TABLEC'. Scan count 999, logical reads 1998, physical reads 0, read-ahead reads 0.
Table 'TABLEA'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0.
Table 'TABLEB'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0.
select [ID] from tablea where [ID] not in(select [ID] from tableb union select [ID] from tablec union select [ID] from tabled)
GO
Table 'TABLED'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0.
Table 'TABLEC'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0.
Table 'TABLEB'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0.
Table 'TABLEA'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLEA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLEA]
GO
CREATE TABLE [dbo].[TABLEA] (
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TABLEA] WITH NOCHECK ADD
CONSTRAINT [PK_TABLEA] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]GO
DECLARE @I as Int
Set @I = 1
WHILE @I < 10000
BEGIN
INSERT INTO [dbo].[TABLEA]([ID])VALUES(@I)
SET @I = @I +1
END
GO
SELECT [ID] FROM [dbo].[TABLEA]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLEB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLEB]
GO
CREATE TABLE [dbo].[TABLEB] (
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TABLEB] WITH NOCHECK ADD
CONSTRAINT [PK_TABLEB] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]GO
DECLARE @I as Int
Set @I = 1000
WHILE @I < 10000
BEGIN
INSERT INTO [dbo].[TABLEB]([ID])VALUES(@I)
SET @I = @I +1
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLEC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLEC]
GO
CREATE TABLE [dbo].[TABLEC] (
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TABLEC] WITH NOCHECK ADD
CONSTRAINT [PK_TABLEC] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]GO
DECLARE @I as Int
Set @I = 500
WHILE @I < 10000
BEGIN
INSERT INTO [dbo].[TABLEC]([ID])VALUES(@I)
SET @I = @I +1
END
GO
SELECT [ID] FROM [dbo].[TABLEC]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLED]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLED]
GO
CREATE TABLE [dbo].[TABLED] (
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TABLED] WITH NOCHECK ADD
CONSTRAINT [PK_TABLED] PRIMARY KEY CLUSTERED
(
[ID]
 
ON [PRIMARY]GO
DECLARE @I as Int
Set @I = 100
WHILE @I < 10000
BEGIN
INSERT INTO [dbo].[TABLED]([ID])VALUES(@I)
SET @I = @I +1
END
GO
SELECT [ID] FROM [dbo].[TABLED]
GO
SET STATISTICS IO ON
go
Select a.[ID], b.[ID], c.[ID], d.[ID]
From TableA a
LEFT JOIN TableB b on a.[ID] = b.[ID]
LEFT JOIN TableC c on a.[ID] = c.[ID]
LEFT JOIN TableD d on a.[ID] = d.[ID]
where b.[ID] is null
AND c.[ID] is null
AND d.[ID] is null
GO
select [ID] from tablea where [ID] not in(select [ID] from tableb union select [ID] from tablec union select [ID] from tabled)
GO
SET STATISTICS IO OFF
go
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply