Weekly Tech Lesson: LEFT SEMI JOINs and How To Use Them

When we INNER JOINfrom one table to another table with a column or set of columns and we select all columns, we'll get the results returned for both tables. This is true for some other join types as well, but the focus of this video will be for INNER JOINs since a LEFT SEMI JOIN is useful in contexts where we want specific results. In some cases with our joins, we only want the result set for one table, not both. Suppose that we wanted the result set for the LEFT table only in the join condition? We could use the standard SQL syntax of selecting all columns in the first table with the * operator filtered on the first table. This would return all columns in the first table and we'd still have the option of selecting columns from the second (and latter) tables. However, if we want to give ourselves the option of only selecting from the first table in our join, we can use a LEFT SEMI JOIN. In the video, SQL Basics: How To Use A LEFT SEMI JOIN and Why, we look at using this functionality.

For this video, we use a self join table since our join will only involve one table. Since we join the table onto itself, any column that we use in the join condition will match provided that we don't change any part of the condition (which we do in our example and I show why). As a quick reminder, not all SQL languages may support the exact syntax shown. Different SQL languages may have different approaches to solving the same problem or developing the same solution. One advantage of LEFT SEMI JOINs over INNER JOINs is possible development issues that may occur with an INNER JOIN. When we use an INNER JOIN, we'll have access to both table's columns when we reference them, but this may not be what we want - we may only want the columns from one table without there being a possible issue of another column being referenced. As we see with LEFT SEMI JOINs, we can accomplish this and avoid this problem using these. As an added bonus here, there can be some security advantages to using these, if we want a table's columns to be completely excluded in a join, but referenced (though there are alternative development techniques for this that are superior). Finally, this can be extremely useful if we want to only check if records in one table exist in another table where references may possible clash.

Given that fat finger and code issues occur, a LEFT SEMI JOIN can help us restrict the result set. In a business situation where we want to check a table's values against another table (or set of tables) and whether those values exist in other tables while only returning the results of the first table, a LEFT SEMI JOIN can help us. This functionality absolutely prevents us from possibly returning data in other tables, whereas using an INNER JOIN would allow us to possibly select columns from other tables than the first. For this reason, a LEFT SEMI JOIN can also be a more secure join type, if our business problem is one of filtering values in one tables that exist in other tables.