Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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')

share|improve this question
    
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 Sep 22 '12 at 8:53

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
    
good comments about providing optimizer with help to make the best use of it as is... very useful information. +1 –  RThomas Sep 24 '12 at 1:23

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.

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.