EXCEPT Operator not returning what expected

Question

Post reply

EXCEPT Operator not returning what expected

sqlstar2011

SSCertifiable

Points: 6000
More actions
February 20, 2008 at 3:38 pm

#186906

I am running following sql statement and getting a result set of 785 rows:
select * from warehouse32.dbo.tmt_dimension
except
select * from warehouse32.dbo.CurrentTMTData
warehouse32.dbo.tmt_dimension has 2330 rows
warehouse32.dbo.CurrentTMTData has 0 rows.
shouldnt I be getting 2330 rows in my result set.
bot tables are exactly the same. all the columns in both tables are not null. here is the ddl for both:
USE [Warehouse32]
GO
/****** Object: Table [dbo].[CurrentTMTData] Script Date: 02/20/2008 17:37:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CurrentTMTData](
[HYP_CLEARANCE] [nvarchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_COUNTRY] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_EMP_TYPE] [nvarchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_EMPNO] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_ORG] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_STATUS] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_DATE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_ACTION] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_COUNTER] [float] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [Warehouse32]
GO
/****** Object: Table [dbo].[TMT_Dimension] Script Date: 02/20/2008 17:37:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TMT_Dimension](
[HYP_CLEARANCE] [nvarchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_COUNTRY] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_EMP_TYPE] [nvarchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_EMPNO] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_ORG] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_STATUS] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_DATE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_ACTION] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[HYP_COUNTER] [float] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
What could be the reason for not getting all the rows from warehouse32.dbo.tmt_dimension?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

Except returns DISTINCT rows. Try running this query over warehouse32.dbo.tmt_dimension. This should return 785 rows.

select distinct

[HYP_CLEARANCE] ,

[HYP_COUNTRY] ,

[HYP_EMP_TYPE],

[HYP_EMPNO] ,

[HYP_ORG],

[HYP_STATUS],

[HYP_DATE] ,

[HYP_ACTION] ,

[HYP_COUNTER]

from warehouse32.dbo.tmt_dimension

My blog: http://jahaines.blogspot.com

sqlstar2011 SSCertifiable Points: 6000 More actions · Answer 2

ahh... so operator can I use to get the difference between two tables. In oracle there is 'MINUS' operator. what is SQL Server equivalent to that?

Thanks

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

You would have to do a left join. The left join would return all records from the left table where they do not exist in the right table. This is not the problem though. The problem is that your warehouse32.dbo.tmt_dimension table has duplicate data. You should remove all redundant data. There is no need to store the same data over 1700 times is there?

The query to return all records that do not match.

SELECT column_list

FROM TableA a

LEFT JOIN TableB b ON a.ID = b.ID

WHERE b.ID IS NULL

My blog: http://jahaines.blogspot.com