Skip to content

Instantly share code, notes, and snippets.

@kyle-eshares
Created December 14, 2016 02:36
Show Gist options
  • Save kyle-eshares/2b7e3a335b752dc1eca33011ce3354ba to your computer and use it in GitHub Desktop.
Save kyle-eshares/2b7e3a335b752dc1eca33011ce3354ba to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment