MySQL outer join SQL tutorial/examples

First of all, outer joins are for instances where info is being looked up on another table and you want rows returned even though nothing was matched for a particular id, e.g.:

create table food (
id int unsigned primary key auto_increment,
food char(16),
yummy char(1),
animal char(16)
);

create table animals (
name char(16) primary key,
weight int unsigned
);

insert into food values
(NULL, 'apple', 'Y', 'human'),
(NULL, 'oranges', 'Y', 'human'),
(NULL, 'apple', 'N', 'dog'),
(NULL, 'oranges', 'N', 'dog'),
(NULL, 'apple', 'N', 'cat'),
(NULL, 'oranges', 'N', 'cat');

insert into animals values
('cat', 12),
('dog', 25);

table 'food'

id food yummy animal
--- ---- ----- ------
1 apple Y human
2 oranges Y human
3 apple N dog
4 oranges N dog
5 apple N cat
6 oranges N cat


table 'animals'

name weight
---- ------
Cat 12
Dog 25

Let's say you want a list of all the foods and the weight of the animals.

If you do:

select f.*, a.weight
from food f, animals a
where f.animal = a.name

In this case the result would be:

+----+---------+-------+--------+--------+
| id | food | yummy | animal | weight |
+----+---------+-------+--------+--------+
| 5 | apple | N | cat | 12 |
| 6 | oranges | N | cat | 12 |
| 3 | apple | N | dog | 25 |
| 4 | oranges | N | dog | 25 |
+----+---------+-------+--------+--------+


Notice that humans is missing, but if you want the humans to show up even
though there's no human entry in the 'animals' table you have to do an
outer join (aka LEFT [OUTER] JOIN in Mysql):

select f.*, a.weight
from food f
LEFT JOIN animals a on (f.animal=a.name)

In this case the result would be:

+----+---------+-------+--------+--------+
| id | food | yummy | animal | weight |
+----+---------+-------+--------+--------+
| 1 | apple | Y | human | NULL |
| 2 | oranges | Y | human | NULL |
| 3 | apple | N | dog | 25 |
| 4 | oranges | N | dog | 25 |
| 5 | apple | N | cat | 12 |
| 6 | oranges | N | cat | 12 |
+----+---------+-------+--------+--------+

Here are some more examples:

SELECT T.name, V.value, T.unit, T.id, A.Product_id
FROM attribute_type AS T
LEFT JOIN attribute AS A ON ( T.id = A.type_id AND A.Product_id = 21 )
LEFT JOIN attribute_value AS V ON ( A.value_id = V.id );

select j.id, j.job_id, d.NAMES, e.DETAILS1, j.file_name, j.job_owner, j.import_date
from db.summary_jobs j
left join db.EST e on (j.job_id=e.JOB_NUMBER)
left join db.DEB d on (e.DEBTOR=d.AC_NO)
order by j.job_id desc limit

Thank you for reading this article MySQL outer join SQL tutorial/examples With URL http://x-tutorials.blogspot.com/2005/04/mysql-outer-join-sql-tutorialexamples.html. Also a time to read the other articles.