When using relational databases you will need to use multiple tables to find what you need. Unlike Excel data is rarely modelled and formatted in one big flat table. Venn diagrams are used in many JOIN guides to explain what they mean, but I prefer something more visual.
Use an INNER join, or just a JOIN, when you want to find the match between two tables. You will need to have a column on both tables that you join ON, and that’s where the match happens. Any results where there is not a match are discarded.
select o.order_item, i.inventory_item from orders o inner join inventory i on o.order_item = i.inventory_item
Use a LEFT JOIN when you want to find a match between two tables, but also show a NULL where there is no match from the right table. A RIGHT JOIN does the same but in reverse.
Like the INNER JOIN, you need a column to join ON. Unlike the INNER JOIN, a NULL is used to show there is no match between the two tables.
select o.order_item, -- this is the left table i.inventory_item -- this is the right table from orders o left join inventory i on o.order_item = i.inventory_item
A CROSS JOIN joins everything with everything. There is no need to provide a column to join on, and it can result in a very big data set. Proceed with caution.
select o.order_item, i.inventory_item from orders o cross join inventory i
While a JOIN combines columns horizontally a UNION combines rows vertically. This is technically a set operator rather than a JOIN but as we are talking about combining datasets this is a good opportunity to introduce it.
Using a UNION combines the result of two queries into one column and removes duplicates.
If your query has multiple columns, they need to be in the same order to complete the UNION.
select * from orders union select * from inventory
The UNION ALL combines the results of two queries the same as a UNION but keeps the duplicates in the result.
select * from orders union all select * from inventory