2

First, I'm just asking about inner joins. And, I'm thinking self joins or reflexive joins through a join table are special cases which depend on how the data is structured (e.g. city - state - country, child - parent - grandparent).

But for the rest of joining tables there's no need to repeat a table in any given "chain"?

(What I mean by "chain":

               __companies -- locations AS L1 -- N Joined Tables -- locations AS L2
SELECT people |
               __schools -- locations AS L3

So, as best I can figure there is a necessary duplication of L1 and L3 since they're unconnected searches. But any conditions on a search of L2 could be placed on a search of L1.

Is this universally true? Is this generally true?

2 Answers 2

4

Dependent on context (chain) addressing to the same table can mean different sets of rows. So you have to point them by two different aliases to avoid ambiguity.

Let's imagine we want to build the list of customers and their suppliers both with cities where they reside:

SELECT w.name AS customer,
       s.city AS c_city,
       z.name AS supplier,
       r.city AS s_city
  FROM customers      AS w
  LEFT JOIN suppliers AS z ON z.id = w.supplier_id
  LEFT JOIN cities    AS s ON s.id = w.city_id
  LEFT JOIN cities    AS r ON r.id = z.city_id
ORDER BY w.name ASC;

Here same table is joined twice because each join performed on it's own condition and produce different resulting city names - one for customer and one for supplier. And there is no way to achieve the desired result without joining the same table twice.

P.S.

Let's imagine we have the list of cities visited by tourist:

-------------------------------------
| ID | DATE | CITY_ID | PREVIOUS_ID |
-------------------------------------

Here PREVIOUS_ID is the reference to the row in the same table and it mean " the city tourist come from". And now we want to build the route containing last four visited cities to the some point:

SELECT * 
   FROM visited      AS w
   JOIN cities       AS z ON z.id = w.city_id
   LEFT JOIN visited AS r ON r.id = w.previous_id
   LEFT JOIN cities  AS s ON s.id = r.city_id
   LEFT JOIN visited AS q ON q.id = r.previous_id
   LEFT JOIN cities  AS d ON d.id = q.city_id
   LEFT JOIN visited AS x ON x.id = q.previous_id
   LEFT JOIN cities  AS f ON f.id = x.city_id
 WHERE w.id = 12345; -- last position

As you can see we can easely join again and again the same tables.

6
  • I'm not sure I understand what you're saying. If you're talking about how I would search given this setup, that I know. I'm asking if there are cases where I would need to include the same table in the chain. In the example could I leave out L2, and place the search on rows in L1 and get the same result?
    – MCB
    May 28, 2014 at 19:42
  • I've update the answer.
    – Kondybas
    May 28, 2014 at 20:23
  • Ok, that confirms the loading of tables on different chains. But what if you wanted to set some condition on cities s, say languages, but only languages spoken in certain cities (I know it's getting awfully contrived). There would never be a reason to join another cities table on top of languages. You could put all your conditions on s and get your results, yes? And if not "never" this would hold true for most usages?
    – MCB
    May 28, 2014 at 20:55
  • I can't get what do you mean "to set some condition on cities"
    – Kondybas
    May 28, 2014 at 21:02
  • 1
    Mmmm, seems I've got it. I'll edit the answer one more time.
    – Kondybas
    May 28, 2014 at 21:09
1

Taking the example from your original question, it is possible that the companies table has two foreign key columns both pointing at the locations table. One could be, say, HeadOfficeLocationID and the other FactoryLocationID. If you required both head office's address and the factory's address to be returned from a query you would have to join companies to locations twice, but use different ON clause predicates.

Edit: Clarifying code examples Taken from AdventureWorks DB in SQL Server 2008R2.

This is sensible, indeed necessary, to retrieve according to two foreign keys:

select
    h.SalesOrderID
    ,a_b.AddressLine1
    ,a_s.AddressLine1
from Sales.SalesOrderHeader as h
inner join Person.Address as a_b
    on a_b.AddressID = h.BillToAddressID
-- 2nd foreign key, 2nd reference to Address, different join predicates:
inner join Person.Address as a_s
    on a_s.AddressID = h.ShipToAddressID;

This is a waste of resources:

select
    h.SalesOrderID
    ,a1.AddressLine1
    ,a2.AddressLine1
from Sales.SalesOrderHeader as h
inner join Person.Address as a1
    on a1.AddressID = h.BillToAddressID
inner join Person.Address as a2
    -- same foreign key
    on a2.AddressID = h.BillToAddressID
-- different predicate on each alias
where a1.City = 'Cloverdale'
and a2.PostalCode between 6000 and 6999;
2
  • I think you're getting at the meat of my question better. So, what you're saying is if would join the table with different ON clauses (e.g. because of two foreign keys) then you would need to repeat loading the table. Otherwise, not.
    – MCB
    May 29, 2014 at 13:11
  • @MCB - code examples added. May 30, 2014 at 1:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.