Using the KB database of non-fiction digital books again, which books have a keyword containing 'strono'?
select b.title,k.keyword from books b left join keywords k on b.titleid=k.titleid where keyword like '%strono%'; title keyword -------------------------- ---------- Astronomy Demystified Astronomy Wonders Beyond Numbers Astronomy Science and Cooking Gastronomy Astronomical Algorithms 2E Astronomy
Now reversing the join:
select b.title,k.keyword
from keywords k
left join books b
on b.titleid=k.titleid
where keyword like '%strono%';
title keyword
-------------------------- ----------
Astronomy Demystified Astronomy
Wonders Beyond Numbers Astronomy
Science and Cooking Gastronomy
Astronomical Algorithms 2E Astronomy
Astronomy
Note the use of an alias (b,k) for the 2 tables to clarify and shorten our request.
Here we see that a keyword 'Astronomy' does not have a matching titleid record in the 'books' table. Now we need to resolve that.
select * from keywords where keyword ="Astronomy"; id keyword authorid titleid ---- --------- -------- ------- 226 Astronomy 168 355 883 Astronomy 409 442 2217 Astronomy 702 770 2687 Astronomy 98765 98765 select title from books where titleid=98765; select author from authors where authorid=98765; author ---------------- Fubar,Octothorpe
Well, it seems this author did NOT write any books with the keyword 'Astronomy'.
In fact this author only exists as an entry in the authors table, so we should probably delete this culprit from both the keywords and authors tables. I will leave that as an exercise for the reader.
So depending on what you are trying to achieve, chosing which table is considered the left one, may or will change the results.
While you should be able to understand and use SQL joins in your database work, they can be very complex and sometimes return wrong or incomplete results. Here is a collection of common SQL join problems: SQL Join Problems