Using DISTINCT inner join in SQL

sql distinct inner-join
Mats Fredriksson picture Mats Fredriksson · Oct 2, 2008 · Viewed 348.7k times · Source

I have three tables, A, B, C, where A is many to one B, and B is many to one C. I'd like a list of all C's in A.

My tables are something like this: A[id, valueA, lookupB], B[id, valueB, lookupC], C[id, valueC]. I've written a query with two nested SELECTs, but I'm wondering if it's possible to do INNER JOIN with DISTINCT somehow.

SELECT valueC
FROM C
INNER JOIN
(
    SELECT DISTINCT lookupC
    FROM B INNER JOIN
    (
        SELECT DISTINCT lookupB
        FROM A
    ) 
    A2 ON B.id = A2.lookupB
) 
B2 ON C.id = B2.lookupC

EDIT: The tables are fairly large, A is 500k rows, B is 10k rows and C is 100 rows, so there are a lot of uneccesary info if I do a basic inner join and use DISTINCT in the end, like this:

SELECT DISTINCT valueC
FROM 
C INNER JOIN B on C.id = B.lookupB
INNER JOIN A on B.id = A.lookupB

This is very, very slow (magnitudes times slower than the nested SELECT I do above.

Answer

Darrel Miller picture Darrel Miller · Oct 2, 2008

I did a test on MS SQL 2005 using the following tables: A 400K rows, B 26K rows and C 450 rows.

The estimated query plan indicated that the basic inner join would be 3 times slower than the nested sub-queries, however when actually running the query, the basic inner join was twice as fast as the nested queries, The basic inner join took 297ms on very minimal server hardware.

What database are you using, and what times are you seeing? I'm thinking if you are seeing poor performance then it is probably an index problem.

Related questions

Eliminating duplicate values based on only one column of the table

My query: SELECT sites.siteName, sites.siteIP, history.date FROM sites INNER JOIN history ON sites.siteName = history.siteName ORDER BY siteName,date First part of the output: How can I remove the duplicates in siteName column? I want to …

Read More
How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

My table is: id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 3 | 10 | 03/03/2009 | john | 300 4 | 11 | 03/03/2009 | juliet | 200 6 | 12 | 03/03/2009 | borat | 500 7 | 13 | 24/12/2008 | borat | 600 8 | 13 | 01/01/2009 | borat | 700 I need to select each distinct home holding the maximum value of datetime. Result would be: id home datetime …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More