Alternative query to this (avoid DISTINCT)

Question

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]( [UserID] [int] NULL, [User] [nvarchar](50) 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]( [CountryID] [int] NULL, [Country] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (1, N'MX') INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (2, N'USA') INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (3, N'CAN') INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (4, N'ENGLAND') INSERT [dbo].[Countries] ([CountryID], [Country]) VALUES (5, N'BRAZIL')

the significance of your data sample would be improved if you add a country to the countries table that is not referenced by a user. currently the result set of the query you are looking for is the same as of select Country from Countries . — miracle173
– miracle173, Commented Sep 22, 2012 at 8:53

Rob Farley · Accepted Answer · 2012-09-22 07:08:49Z

The two scripts in RThomas' answer are both useful. You could also use GROUP BY, which gives a similar advantage to RThomas' methods, but keeping a similar form to your original query.

select country from Users inner join countries on users.CountryID=countries.CountryID GROUP BY countries.CountryID, countries.country;

The reason why you group by CountryID is that it's the primary key of your countries table, giving the Query Optimizer some better options.

...except that it's not in your scripts.

Put PKs (with Clustered Indexes) on your tables, and a FK relationship between them. Index CountryID in the Users table, and put a Unique Index on the Country field.

Once you've done all that, using DISTINCT how you have will actually give you the ideal execution plan.

good comments about providing optimizer with help to make the best use of it as is... very useful information. +1 — RThomas
– RThomas, Commented Sep 24, 2012 at 1:23

RThomas · Accepted Answer · 2012-09-21 21:33:23Z

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.

Erwin Brandstetter · Accepted Answer · 2012-09-22 15:06:47Z

And there is the basic approach to GROUP BY in the n-table and then JOIN to the 1-table in a 1:n relationship. This is often faster than joining before the grouping, but I am not sure whether it makes a difference for SQL-Server 2008.

SELECT country FROM ( SELECT CountryID FROM Users GROUP BY CountryID ) AS u JOIN countries AS c ON u.CountryID = c.CountryID

Stack Exchange Network

Alternative query to this (avoid DISTINCT)

3 Answers 3

Hot Network Questions

Alternative query to this (avoid DISTINCT)

3 Answers 3

Related

Hot Network Questions