When should we use each type of
JOIN covered in the lesson?
Depending on how you need to select the data, each type of
JOIN may accomplish a specific goal.
JOIN (or INNER JOIN)
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 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 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
main course, and
dessert. Using a
CROSS JOIN can give us every possible combination.
Although not necessarily a
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.