gistfile1.txt

Author
------
authors.id | authors.name
-------------------
1         |  "paul"
2         |  "peter"
3         |  "john"

Books
-----
books.id    | books.title     | books.author_id
-----------------------------------------
1           | "Moby Dick"     | 1
2           | "Nada"          | NULL
3           | "Blammo"        | 2
4           | "Kazzam"        | 2

###
### LEFT JOIN FORWARD
### (Querying All Books)

SELECT books.id, books.title, books.author_id, authors.id, authors.name
FROM books
LEFT JOIN authors ON authors.id = books.author_id;

books.id    | books.title     | books.author_id   | authors.id | authors.name
-------------------------------------------------------------------------
1           | "Moby Dick"     | 1                 | 1          | "paul"
2           | "Nada"          | NULL              | NULL       | NULL
3           | "Blammo"        | 2                 | 2          | "peter"
4           | "Kazzam"        | 2                 | 2          | "john"

###
### INNER JOIN FORWARD
### (Querying Books that have an author)

SELECT books.id, books.title, books.author_id, authors.id, authors.name
FROM books
INNER JOIN authors ON authors.id = books.author_id;

books.id    | books.title     | books.author_id   | authors.id | authors.name
-------------------------------------------------------------------------
1           | "Moby Dick"     | 1                 | 1          | "paul"
3           | "Blammo"        | 2                 | 2          | "peter"
4           | "Kazzam"        | 2                 | 2          | "john"


###
### LEFT JOIN FORWARD FILTER
### (Querying Book should be written by 'paul')

SELECT books.id, books.title, books.author_id, authors.id, authors.name
FROM books
INNER JOIN authors ON authors.id = books.author_id
WHERE author.name = 'paul';

books.id    | books.title     | books.author_id   | authors.id | authors.name
-------------------------------------------------------------------------
1           | "Moby Dick"     | 1                 | 1          | "paul"



###
### LEFT JOIN REVERSE
### (Querying all authors)

SELECT authors.id, authors.name, books.id, books.title, books.author_id
FROM authors
LEFT JOIN books ON books.author_id = authors.id;

authors.id | authors.name | books.id    | books.title     | books.author_id
----------------------------------------------------------------------------
1         |  "paul"       | 1           | "Moby Dick"     | 1
2         |  "peter"      | 2           | "Blammo"        | 2
2         |  "peter"      | 2           | "Kazzam"        | 2
3         |  "john"       | NULL        | NULL            | NULL

###
### INNER JOIN REVERSE
### (Querying author, should have a book)

SELECT authors.id, authors.name, books.id, books.title, books.author_id
FROM authors
INNER JOIN books ON books.author_id = authors.id;

authors.id | authors.name | books.id    | books.title     | books.author_id
----------------------------------------------------------------------------
1         |  "paul"       | 1           | "Moby Dick"     | 1
2         |  "peter"      | 2           | "Blammo"        | 2
2         |  "peter"      | 2           | "Kazzam"        | 2

###
### INNER JOIN REVERSE FILTER
### (Querying author, should have either "Moby Dick")

SELECT authors.id, authors.name, books.id, books.title, books.author_id
FROM authors
INNER JOIN books ON books.author_id = authors.id
WHERE books.title = "Moby Dick";

authors.id | authors.name | books.id    | books.title     | books.author_id
----------------------------------------------------------------------------
1         |  "paul"       | 1           | "Moby Dick"     | 1

###
### INNER JOIN REVERSE FILTER OR
### (Querying author, should have either "Moby Dick" or "Kazzam")

SELECT authors.id, authors.name, books.id, books.title, books.author_id
FROM authors
INNER JOIN books ON books.author_id = authors.id
WHERE books.title = "Moby Dick" OR "Kazzam";

authors.id | authors.name | books.id    | books.title     | books.author_id
----------------------------------------------------------------------------
1         |  "paul"       | 1           | "Moby Dick"     | 1
2         |  "peter"      | 2           | "Kazzam"        | 2

###
### DOUBLE INNER JOIN REVERSE
### (Querying author, Shows setup for next example)

SELECT authors.id, authors.name, B0.id, B0.title, B0.author_id, B1.id, B1.title, B1.author_id
FROM authors
INNER JOIN books B0 ON B0.author_id = authors.id
INNER JOIN books B1 ON B1.author_id = authors.id;

authors.id | authors.name | B0.id       | B0.title        | B0.author_id | B1.id       | B1.title        | B1.author_id
------------------------------------------------------------------------------------------------------------------------
1         |  "paul"       | 1           | "Moby Dick"     | 1            | 1           | "Moby Dick"     | 1
2         |  "peter"      | 2           | "Blammo"        | 2            | 2           | "Blammo"        | 2
2         |  "peter"      | 2           | "Blammo"        | 2            | 3           | "Kazzam"        | 2
2         |  "peter"      | 3           | "Kazzam"        | 2            | 2           | "Blammo"        | 2
2         |  "peter"      | 3           | "Kazzam"        | 2            | 3           | "Kazzam"        | 2

###
### DOUBLE INNER JOIN REVERSE FILTERED
### (Querying author, should have both books "Blammo" and "Kazzam")

SELECT authors.id, authors.name, B0.id, B0.title, B0.author_id, B1.id, B1.title, B1.author_id
FROM authors
INNER JOIN books B0 ON B0.author_id = authors.id
INNER JOIN books B1 ON B1.author_id = authors.id
WHERE B0.title = "Blammo" AND B1.title = "Kazzam";

authors.id | authors.name | B0.id       | B0.title        | B0.author_id | B1.id       | B1.title        | B1.author_id
------------------------------------------------------------------------------------------------------------------------
2         |  "peter"      | 2           | "Blammo"        | 2            | 3           | "Kazzam"        | 2