Just giving credit where credit is due: these are my notes from A udemy course I am taking

Basic overview

Combine 2 tables with a join based on a column that is the same between them. The different joins are just different ways to deal with data that is only in one of the joined tables.

AS statement: Makes an alias for a table

Blog overview

There is a good write up over on. https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/. This is what I am summarizing, his stuff explains this better.
Think of a Venn diagram with table A on the left and table B on the right

INNER JOIN: Show where the 2 tables overlap: Return only the set of recoded that match in both tables

FULL OUTTER JOIN: Show all data, where the data is in table A and not table B the feilds in table B will have values of null

LEFT OUTER JOIN: Returns all the data from table A, if there is data that matches up in table B it’s included. If the data from table B isn’t there then those peaces of data are NULL

AS

AS: Make an alias for a column so you can reference the alias instead of the full name

SELECT SUM(payments_made) AS total_payments FROM payments;

The AS operator gets executed at the end of a query, so you can’t use the ALIAS inside a WHERE operator

INNER JOIN

Will result with the data that match in BOTH tables. Thus table order doesn’t matter. Inner join is the default (So if you see JOIN that’s the same thing as INNER JOIN).

This will work, but will duplicate the student ID field:

SELECT * FROM table_a INNER JOIN table_b on table_a.student_id = table_b.student_id

To have no dup fields select only one of them
SELECT name, table_a.student_id, address FROM table_b INNER JOIN table_a ON table_b table_a.student_id = table_b.student_id

select first_name, last_name, customer.customer_id, amount from payment inner join customer on payment.customer_id = customer.customer_id;

SELECT email, district FROM customer
INNER JOIN address
ON address.address_id = customer.address_id
WHERE district = ‘California’;

FULL OUTER JOIN

All data is returned: any data without a matching pair from the other table has a value of NULL

SELECT * FROM table_a FULL OUTER JOIN table_b ON table_a.student_id = table_b.student_id

To find where the data is missing you can use the where statement

SELECT * FROM table_a FULL OUTER JOIN table_b ON table_a.student_id = table_b.student_id WHERE table_a.random_column_name IS null or table_b.random_column_name IS null;

LEFT OUTER JOIN

Do a full join and pull everything from the left table (the table that your FROM statement references is on the “left”)

This pulls everything from table_a and if corresponding data in table_b doesn’t exist it that data is null. Nothing that is in table_b only gets returned

SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.student_id = table_b.student_id

So the call below will make the “film” the left table, and bring in all the data from the right table “inventory” about it. If there isn’t data in inventory then that data will be null

SELECT film.film_id, title, inventory_id, store_id FROM film LEFT OUTER JOIN inventory ON inventory.film_id = film.film_id;

RIGHT JOIN

Same as left join but tables are swapped. Honestly no clue why you would use this very often instead of just flipping the tables in the statement.

SELECT * FROM table_a RIGHT OUTER JOIN table_b ON table_a.student_id = table_b.student_id

UNION

Done to combine 2+ select statements

SELECT name, value FROM sales2000 UNION SELECT name, value FROM sales2001

Multiple Joins

You can join multiple joins together

select first_name, last_name, title from actor
INNER JOIN film_actor
on film_actor.actor_id = actor.actor_id
INNER JOIN film
on film.film_id = film_actor.film_id
where first_name = ‘Nick’ and last_name = ‘Wahlberg’;