When should we use each type of JOIN?


#1

Question

When should we use each type of JOIN covered in the lesson?

Answer

Depending on how you need to select the data, each type of JOIN may accomplish a specific goal.

JOIN (or INNER JOIN)
Use JOIN, or INNER JOIN, when you want to strictly select rows of data that match some condition, provided by an ON clause. For example, if we had tables of employee information, and require their company information, we could JOIN the tables together so that we only obtain results that match, and provide us all the information we need, excluding results that are missing information.

LEFT JOIN
LEFT JOIN can be used when you want to see all the results from the first table no matter what, but also want to include matches, if any, with the second table. For example, say we had a table for customers and another table for purchase information. If we wanted to obtain all customer information, and any purchase information, then a LEFT JOIN might be useful. If the customers made no purchases, their information will still be returned.

CROSS JOIN
CROSS JOIN can be used when you want to get combinations of rows from a table with other tables. A simplified example of using this would be, say we wanted to get every possible combination of a meal, which consists of an appetizer, main course, and dessert. Using a CROSS JOIN can give us every possible combination.

UNION
Although not necessarily a JOIN, UNION can be used when you wish to combine multiple tables together quickly. One helpful feature of a UNION is that it will only return unique rows, so there will be no duplicates in the combined table.