Travis Peterson Travis Peterson - 7 months ago 5
SQL Question

SQL efficient solution for mutilple joins (could be 10 joins) on same table (lookup table)

We've implemented a lookup table that contains an ID and a Text field. My table then only consists of the LookupID for different field values. For example...

Table


  • TableID, StatusID, TypeID, DocID



Values would look something like this


  • 1, 2, 3, 4



LookupTable


  • LookupID, Text



Values would look something like this


  • 1, SomeValue

  • 2, My Status Text

  • 3, My Type Text

  • 4, My Document Text



What I need to know is if this is most efficient solution for a query on these tables (creating a sql view of the data with the text displayed instead of the ID)

SELECT T.TableID, L1.Text as StatusText, L2.Text as TypeText, L3.Text as DocText
FROM Table T
LEFT JOIN LookupTable L1 on L1.LookupID = T.StatusID
LEFT JOIN LookupTable L2 on L2.LookupID = T.TypeID
LEFT JOIN LookupTable L3 on L3.LookupID = T.DocID


... or do subqueries work better for this solution? Something like this.

SELECT T.TableID,
(SELECT L.Text FROM LookupTable L WHERE L.LookupID = T.StatusID) as StatusText,
(SELECT L.Text FROM LookupTable L WHERE L.LookupID = T.TypeID) as TypeText,
(SELECT L.Text FROM LookupTable L WHERE L.LookupID = T.DocID) as DocText
FROM Table T


... Or is there a better solution? Keep in mind I only joined 3 times for this example and will need to join 10 or more times.

Answer

Your best bet is likely to create separate lookup tables for each separate entity in your database. This gives you better flexibility in the future, should you need to add an additional attribute for a single lookup type (for example, now you need to track state birds for each state, but that certainly won't be relevant for car models). In my experience "generic" database design patterns usually turn out bad. Design with a purpose.

Once you have that, as long as you have the appropriate indexes having multiple JOINs will almost always (if not always) perform better than subqueries:

SELECT
    P.person_id,
    S.state_name,
    G.movie_genre_name,
    ...
FROM
    Person P
INNER JOIN [State] S ON S.state_id = P.home_state_id
INNER JOIN Movie_Genre G ON G.movie_genre_id = P.favorite_movie_genre_id
...

Also, keep in mind that every list in your system doesn't have to necessarily be a lookup table. Something like gender, for example, can be maintained simply through a CHECK CONSTRAINT:

gender VARCHAR(15) NULL CONSTRAINT CHK_Person_Gender CHECK (gender IN ('Male', 'Female', 'Transgender'))

or:

severity VARCHAR(10) NOT NULL CONSTRAINT CHK_Ticket_Severity CHECK (severity IN ('High', 'Medium', 'Low'))

This is relevant for lists that are basically just a name. Lists that have items with additional attributes or lists that might change frequently over time should be put into a table.