T-SQL: Can I query one view for values and then query a second view only for values that don't exist in the first?

user_109941 0
Viewed 49
DROP TABLE IF EXISTS ..Players
DROP TABLE IF EXISTS ..FreeAgents

CREATE TABLE [dbo].[FreeAgents](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [Position] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_FreeAgents] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Players](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [Position] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Players] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[FreeAgents] ON 
GO
INSERT [dbo].[FreeAgents] ([Id], [FirstName], [LastName], [Position]) VALUES (2, N'Julian', N'Edelman', N'WideReceiver')
GO
INSERT [dbo].[FreeAgents] ([Id], [FirstName], [LastName], [Position]) VALUES (3, N'Dez', N'Bryant', N'WideReceiver')
GO
INSERT [dbo].[FreeAgents] ([Id], [FirstName], [LastName], [Position]) VALUES (4, N'Brandon', N'Jacobs', N'DefensiveEnd')
GO
SET IDENTITY_INSERT [dbo].[FreeAgents] OFF
GO
SET IDENTITY_INSERT [dbo].[Players] ON 
GO
INSERT [dbo].[Players] ([Id], [FirstName], [LastName], [Position]) VALUES (1, N'Tom', N'Brady', N'Quarterback')
GO
INSERT [dbo].[Players] ([Id], [FirstName], [LastName], [Position]) VALUES (2, N'Cam', N'Newton', N'Quarterback')
GO
INSERT [dbo].[Players] ([Id], [FirstName], [LastName], [Position]) VALUES (3, N'Julian', N'Edelman', N'WideReceiver')
GO
SET IDENTITY_INSERT [dbo].[Players] OFF
GO

SELECT FirstName, LastName, Position FROM ..Players
UNION
SELECT FirstName, LastName, Position FROM ..FreeAgents

The code above returns five rows. This is correct.

FirstName LastName Position
Brandon Jacobs DefensiveEnd
Cam Newton Quarterback
Dez Bryant WideReceiver
Julian Edelman WideReceiver
Tom Brady Quarterback

My Problem:

  • The above code snippet queries and unions two physical tables.
    • My real-world situation involves two computation-intense views.
  • Conceptually, I'd like to return the same result set that a UNION returns.
    • Except that I don't want to query the second table for rows that already exist in the first table.
  • Emphasis: For small tables, this makes no perceptible difference. The SQL Server engine is perfectly capable of filtering out duplicates with the UNION clause.
    • But in my real-world situation, I'm not querying two small physical tables. I'm querying two computation-intense views.
duplicates union view tsql sql-server
1 Answers

If you have some set of keys that are not computation intensive (like id col in your example) I think you best bet would be to use temp table to store results from first view and then union ALL it with second with where check it does not exists in temp table.

SELECT * FROM view1
INTO #v1;

SELECT * FROM #v1
UNITON ALL
SELECT * FROM view2
WHERE NOT EXISTS(SELECT 1 FROM #v1 WHERE #v1.id = view2.id)

The second view should perform computations only for missing records.

Using a WITH statement can be even better but I'm not sure - that needs to be tested on real data.

WITH v1 AS (SELECT * FROM view1)
SELECT * FROM v1
UNION ALL
SELECT * FROM view2
WHERE NOT EXISTS (
  SELECT 1 FROM v1
  WHERE v1.id = view2.id -- Set duplicate condition here
)
user_19750860
Related