All About SQL Joins

For purposes of demonstration, lets imagine we have a zoo.
The zoo database has a table of animals it keeps named Animals.
It also has a table of the soft stuffed animals it sells in the Products table.

You have been asked to provide the following:

Find each Animal where there is not a corresponding Product:

Select A.*, P.*
from Animal A
left join Product P
on A.Animal = P.Product
where P.Product is null


Find all the Animals where there is not a corresponding Product
AND all the Products where there is not a corresponding Animal

Select A.*, P.*
from Product P
full join Animal A
on P.Product = A.Animal
where A.Animal is null or P.Product is null


Find each Animal at the zoo where there is also a corresponding Product:


select A.*, P.*
from Animal A
join Product P
on A.Animal = P.Product




Find each Animal at the zoo and also each Product whether there is a match or not:

Select A.*, P.*
from Animal A
Full join Product P
on A.Animal = P.Product