HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Alternative query to this (avoid DISTINCT)

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thisdistinctalternativequeryavoid

Problem

Note: I work with MSSQL 2008, but I guess it's valid for many others DB engines
I have this table "Users":

UserID User CountryID
1 user 1 1
2 user 2 2
3 user 3 3
4 user 4 4
5 user 5 4
6 user 6 3

And this table "Countries"

CountryID Country
1 MX
2 USA
3 CAN
4 ENGLAND

As you can see, every user belongs to a country.

If I want to know, all the different countries where I have at least, one user on the Users table, right now I do this query:

select distinct country 
from Users inner join
countries on users.CountryID=countries.CountryID


And achieve the next result set:

CAN
ENGLAND
MX
USA

Which is indeed, all the different countries, where I have at least one user on muy table Users.

My doubt is, is possible to achieve the above result set, with out using "DISTINCT", I mean only using JOINS and conditions ?

Here it's de DDL scripts:

USE [TEST]
GO
/ Object: Table [dbo].[Users] Script Date: 09/21/2012 16:21:14 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].Users NULL,
[CountryID] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (1, N'user 1', 1)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (2, N'user 2', 2)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (3, N'user 3', 3)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (4, N'user 4', 4)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (5, N'user 5', 4)
INSERT [dbo].[Users] ([UserID], [User], [CountryID]) VALUES (6, N'user 6', 3)
/ Object: Table [dbo].[Countries] Script Date: 09/21/2012 16:21:14 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].Countries NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (1, N'M

Solution

You could get that same result set with the following:

select country from countries where exists 
    (select countryid from users 
        where users.countryid = country.countryid)


OR

select country from countries where countryid in 
    (select countryid from users)


Neither of these use distinct but other than that I don't know how much "better" they are.

Code Snippets

select country from countries where exists 
    (select countryid from users 
        where users.countryid = country.countryid)
select country from countries where countryid in 
    (select countryid from users)

Context

StackExchange Database Administrators Q#24711, answer score: 7

Revisions (0)

No revisions yet.