Relational Theory – Need to Repeat a Table in Joins?

relational-theory

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?

Best Answer

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.