Using DISTINCT inner join in SQL

Question

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

User · Accepted Answer

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.

User · Answer

Is this what you mean   SELECT DISTINCT C valueC FROM  C INNER JOIN B ON C id   B lookupC INNER JOIN A ON B id   A lookupB

User · Answer

SELECT DISTINCT C valueC  FROM C    LEFT JOIN B ON C id   B lookupC   LEFT JOIN A ON B id   A lookupB WHERE C id IS NOT NULL   I don t see a good reason why you want to limit the result sets of A and B because what you want to have is a list of all C s that are referenced by A  I did a distinct on C valueC because i guessed you wanted a unique list of C s     EDIT  I agree with your argument  Even if your solution looks a bit nested it seems to be the best and fastest way to use your knowledge of the data and reduce the result sets    There is no distinct join construct you could use so just stay with what you already have

User · Answer

I believe your 1 m relationships should already implicitly create DISTINCT JOINs   But  if you re goal is just C s in each A  it might be easier to just use DISTINCT on the outer-most query   SELECT DISTINCT a valueA  c valueC FROM C     INNER JOIN B ON B lookupC   C id     INNER JOIN A ON A lookupB   B id ORDER BY a valueA  c valueC

[sql] Using DISTINCT inner join in SQL

Examples related to sql

Examples related to distinct

Examples related to inner-join