The Status of Our Investigation
We have just found our mysterious company Big Data Crunchers Ltd. in the entity table. Next step: locate its address!
Our objective is to "combine" the entity and intermediary tables to obtain a table that includes each company and its address. In SQL, there are two main ways of writing joins:
The first method: with FROM and WHERE
Remember the chapter Link Relations using Joins? In the section Another Way of Looking at Joins I explained that a join is equivalent to a Cartesian product followed by a restriction.
So I begin by obtaining the Cartesian product of the two tables...
SELECT * FROM entity, address ;
... then I add the restriction condition:
SELECT * FROM entity, address WHERE entity.id_address = address.id_address ;
And there you have it! Our join is made!
The second method: with JOIN and ON
The second method introduces the keywords
SELECT * FROM entity JOIN address ON entity.id_address = address.id_address ;
Join Multiple Columns
If your foreign key contains two attributes (or more), you must use
AND . Take the example of a table
t1 that has a foreign key with two attributes,
fk2 . This foreign key references table
t2 , whose primary key is also made up of two 2 attributes,
-- First method:SELECT * FROM t1, t2 WHERE (t1.fk1 = t2.pk1 AND t1.fk2 = t2.pk2);-- Second method:SELECT * FROM t1 JOIN t2 ON (t1.fk1 = t2.pk1 AND t1.fk2 = t2.pk2);
We have achieved our goal of combining our two tables. We must now find Big Data CrunchersLtd. in the result. To do this, we need a little restriction. We add it in the
WHERE clause. Since there is already a condition, we use
SELECT * FROM entity, address WHERE entity.id_address = address.id_address AND entity.name = 'Big Data Crunchers Ltd.';
Join an Association Table
Now let’s find the intermediaries who helped create the company Big Data Crunchers Ltd.!
Previously, we saw that association tables are used to model many-to-many cardinalities between two objects. Entity and intermediary have a many-to-many relationship, because many intermediaries can create one company, and one intermediary can create many companies. Therefore, we have a table with the name assoc_inter_entity, containing, among other things:
an entity column, a foreign key referencing the entity table,
an intermediary column, a foreign key referencing the intermediary table.
So now we need to join three tables, as follows:
SELECTi.id as intermediary_id,i.name as intermediary_name,e.id as entity_id,e.name as entity_name,e.status as entity_statusFROMintermediary i,assoc_inter_entity a,entity eWHEREa.entity = e.idAND a.inter = i.idAND e.name = 'Big Data Crunchers Ltd.' ;
We specify the three tables in the
FROM clause. In the
WHERE clause, we place the two join conditions, along with the name of our company.
Go Further: Outer Joins
Remember outer joins? They are used to retain rows that would have been left out if you were using an inner join. If we want to obtain a table containing all of the companies of entity and their addresses without losing the companies whose addresses are not known, we need a left outer join.
SELECT *FROM entityLEFT OUTER JOIN address ON entity.id_address = address.id_address;
If, on the other hand, we want to retain all of the addresses (whether they are associated with companies or not), we need a right outer join:
SELECT *FROM entityRIGHT OUTER JOIN address ON entity.id_address = address.id_address;
You see where I’m going with this? Yes! All that remains now is to give you the syntax for the full join! Which will use to retain all of the addresses and all of the companies:
SELECT *FROM entityFULL OUTER JOIN address ON entity.id_address = address.id_address;
Go Further: Natural Joins
In the chapter Link Relations using Joins, I explained the concept of the natural join. Here’s how to implement this concept in SQL:
SELECT * FROM entity NATURAL JOIN address ;
There are two methods for joining:
When we’re dealing with an association table, we have to join three tables!
To simplify queries, we can use aliases.
A Little Exercise!
The database is online. Get some practice by rewriting the queries found in this chapter. Don’t hesitate to change them, play with them, and “see what happens.”