Q:

Test value exists and return boolean accordingly

I'm kind of new to SQL and I have been struggling to get the result I want.
I have these tables :

And my goal is to produce this : 

For translation purposes : emprunt == borrowed, livre == book, oeuvre == collection
So my goal is to get all the books id (id_livre), their collection name (titre) and a boolean :
TRUE if they're borrowed (id_livre in emprunt table)
FALSE otherwise   
I've done this : 
SELECT livre.id_livre, titre, id_emprunt
FROM livre NATURAL JOIN oeuvre LEFT JOIN emprunt ON livre.id_livre = emprunt.id_livre
ORDER BY id_livre;

And got this output : 

So I'm close to what I want I believe but I don't know how to have a test returning a boolean instead of id_emprunt in the 3rd column
Any help appreciated, thanks.

A:

Just use a case expression:
SELECT l.id_livre, l.titre,
       (CASE WHEN e.id_emprunt IS NOT NULL THEN 't' ELSE 'f' END) as est_emprunte
FROM livre l JOIN
     oeuvre o
     USING (id_livre) LEFT JOIN
     emprunt e
     USING (id_livre)
ORDER BY id_livre;

Oracle does not have a built-in boolean type, so you can settle for the letters 't' and 'f'.  If it did (and some databases do) you would use:
   (e.id_emprunt IS NOT NULL) as est_emprunte

Note that I replaced the NATURAL JOIN.  I use the word "abomination" to describe it (partly in contrast to the word "natural" which is not appropriate).  The reason why I strongly oppose using it is because it does not take properly declared foreign key relationships into account.  It is also dangerous, because the columns used for joining are not clear in the query, so I think it is a bad practice for that reason as well.

