patternMinor
Alternative query to this (avoid DISTINCT)
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:
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
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.CountryIDAnd 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:
OR
Neither of these use distinct but other than that I don't know how much "better" they are.
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.