StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFastest way to perform multiple "IN" queries in SQL
text
Body
copied!<p>I have a fairly horrible query using 2 "IN" statements that needs running on our database. Firstly the schema (Simplified for this example):</p> <pre><code>CREATE TABLE [dbo].[SystemUser] ( [SystemUserID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](50) NULL, [Surname] [nvarchar](50) NULL CONSTRAINT [PK_ApplicationUser] PRIMARY KEY CLUSTERED ( [SystemUserID] ASC ) ) GO CREATE TABLE [dbo].[Group] ( [GroupID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED ( [GroupID] ASC ) ) GO CREATE TABLE [dbo].[GroupMembership] ( [SystemUserID] [int] NOT NULL, [GroupID] [int] NOT NULL CONSTRAINT [PK_GroupMembership] PRIMARY KEY CLUSTERED ( [SystemUserID] ASC, [GroupID] ASC ) ) GO </code></pre> <p>What I want to do is find all "SystemUser" records that match a list of SystemUserIDs that do NOT have membership to a "Group" that is in a list of GroupIDs.</p> <p>So 2 seperate lists of IDs beng compared in one query. The fastest way I can think of doing this currently is below:</p> <pre><code>SELECT SU.SystemUserID FROM [dbo].[SystemUser] SU LEFT JOIN ( SELECT GM.SystemUserID FROM [dbo].[GroupMembership] GM WHERE GM.GroupID IN ( 1, 7, 8, 10, 32 ) ) GM ON GM.SystemUserID = SU.SystemUserID WHERE SU.SystemUserID IN ( 10, 61, 80, 93, 98 ) AND GM.SystemUserID IS NULL /* Not matched */ </code></pre> <p>Is there anything I'm missing; would a "WHERE NOT EXISTS" check be more efficient? Or can you think of a better way of processing and filtering by the two lists?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload