Skip site navigation (1) Skip section navigation (2)

Re: Left Join Question

From: ries van Twisk <pg(at)rvt(dot)dds(dot)nl>
To: "Ryan Wells" <ryan(dot)wells(at)soapware(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Left Join Question
Date: 2008-11-18 23:18:11
Message-ID: 769E2C10-DC5B-4D06-8D3F-4AC70EB2E022@rvt.dds.nl (view raw or flat)
Thread:
Lists: pgsql-sql
On Nov 18, 2008, at 5:48 PM, Ryan Wells wrote:

> While looking through our data layer code today, I ran across this  
> query:
>
> SELECT
> tasks.*,
> clients.FirstName,
> clients.LastName,
> clients.MiddleInitial,
> iteminfo.CreatedBy,
> iteminfo.StationId,
> iteminfo.CreatedDate,
> changelog.LastModified,
> changelog.LastModifiedBy,
> changelog.LastModifiedAt,
> ticklers.Due,
> ticklers.Reminder
>
> FROM tasks
>  LEFT JOIN clients ON tasks.ClientId = clients.ClientId
>   LEFT JOIN iteminfo ON tasks.Id = iteminfo.ItemId
>    LEFT JOIN changelog ON tasks.Id = changelog.ItemId
>     LEFT JOIN ticklers ON tasks.Id = ticklers.RelatedId
>
> WHERE tasks.Id = '123456';
>
> (I've cleaned it up so it's  easier to read.)
>
> The basic data structure is that we have a todo list that contains a  
> list of tasks which may or may not be associated with clients,  
> items, log entries, or ticklers (scheduled reminders).
>
> The query works as intended:  it returns a result-set with all the  
> necessary data to display in the todo list.   The performance is not  
> a major concern, although it can be slow for large lists.
>
> Since it works, my question is really more about principles:  Given  
> that each of the tables in question will contain tens of thousands  
> of rows, is a nested join really the best way to approach this?
>
> Thanks!
> Ryan

To answer this better we need to know the exact relations between each  
table.
For example, does each task have 0 or more clients, or 1 or more  
clients?
If it's 1 or more, then you can properly use a JOIN instead of a LEFT  
JOIN, this would make the operation faster I believe.

I hope I did explain myself correctly.

Ries







In response to

pgsql-sql by date

Next:From: Mark RobertsDate: 2008-11-18 23:22:09
Subject: Re: Left Join Question
Previous:From: Ryan WellsDate: 2008-11-18 22:48:33
Subject: Left Join Question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group