It's not your fault, the documentation is very light on the topic of
JOINing ( I have made a feature request to improve this). Joining two
tables works like this....
Draw two circles on a piece of paper, Make sure they overlap to some
degree. Put an A in the left circle not part of the overlap, put a B in
the overlapped area, put a C in the right circle not inside the overlapped
area. Using ASCII art it would look something like this:
/-----\ /-----\
/ \ / \
/ X \
/ / \ \
/ / \ \
| A | B | C |
\ \ / /
\ \ / /
\ X /
\ / \ /
\-----/ \-----/
When you do a JOIN, you are asking the query engine to line up the rows in
one table with rows in another. Optionally, you can say whether you only
want the rows that match or want everything from one table even if it
doesn't match anything in the other table.
Let's imagine for a second that you are a publisher and you have some
auteurs in your data who haven't written any libres and a few libres
without auteurs (story ideas you haven't gotten someone to write, yet). If
we can imagine that the left circle of AB represents all of the auteurs in
your database and the right circle of BC represents your table of libres,
you are ready to understand JOINs. The area B represents where the two
overlap (where libre.auteur=auteur.id)
If you want to know all of the authors and the books they have written
(ignoring those authors without books), you want to get those rows from
area B where the rows of auteurs matches those of libres. This is when
you use an INNER JOIN
SELECT a.nom, a.prenom, l.titre
FROM auteurs a
INNER JOIN libres l
ON l.auter = a.id;
If you want a list of all auteurs in your system, regardless of whether
they have written a libre or not, that would be represented by the area
covered by AB (the whole circle).:
SELECT a.nom, a.prenom, l.titre
FROM auteurs a
LEFT JOIN libres l
ON l.auter = a.id;
The LEFT part of the LEFT JOIN indicates that the entire table on the LEFT
of the predicate is to be returned. In this case the table auteurs is to
the left of the predicate so all of the rows from that table will be part
of the result even if there aren't any rows in libres that match.
To view it the other way, to list all of the libres records without
regards to if they have auter or not we can use the SAME query with one
small change.
SELECT a.nom, a.prenom, l.titre
FROM auteurs a
RIGHT JOIN libres l
ON l.auter = a.id;
See the difference? Now, a special type of query would be to identify all
of the rows in one table that do not match any rows in the other. This
would be what kind of query you would need to run if you wanted to find
auteurs without libres (or vice versa).
SELECT *
FROM auteurs a
LEFT JOIN libres l
ON a.id = l.auteur
WHERE l.id IS NULL;
I am sure you are thinking "The ID field of my libres table could never be
NULL. He must be crazy". It's the design behavior of the outer joins to
return NULLS for EVERY COLUMN in the "other" table if the ON condition is
not met. In this case, you would only see values for the columns that come
from libres if the tables actually matched up. Wherever they don't, you
will see all NULLs.
There are other much more detailed explanations about this on the web but
I hope this can gets you started.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
news <[EMAIL PROTECTED]> wrote on 12/18/2005 10:18:16 AM:
> Hi
>
> Works #1
>
> I tought GROUP BY was a grouping algo not a summarizing instruction.
>
> I have problems understanding the meaning of INNER, LEFT etc.
> Does LEFT mean that the joined table will be placed before the actual
table
> ? meaning that calculations will be made against the joined table first
?
>
> Could you esplain these terms a bit to me please ?
>
> I think these terms are the most ambigous to the beginners like me.
> I read the documentation but I'm not sure I understand it very well.
>
> Thanks
>
> Sandy
>
>
> > Here is how I prefer to write the query you wrote (using an explicit
JOIN)
> >
> > SELECT livres.*
> > , auteurs.id as auid
> > , CONCAT(auteurs.nom,', ',auteurs.prenom) as nomauteur
> > FROM livres
> > INNER JOIN auteurs
> > ON auteurs.id = livres.auteur
> > ORDER BY livres.titre;
> >
> > That would find all books with auteurs, however it would not locate
any
> > books without auteurs. To list all books regardless of whether they
have a
> > matching auteur, you need to use one of the OUTER joins like this:
> >
> > SELECT livres.*
> > , auteurs.id as auid
> > , CONCAT(auteurs.nom,', ',auteurs.prenom) as nomauteur
> > FROM livres
> > LEFT JOIN auteurs
> > ON auteurs.id = livres.auteur
> > ORDER BY livres.titre;
> >
> >
> > That will list every book and any auteurs should they exist. Please
let me
> > know if this gets you going.
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> >
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
