Left and right joins do the exact same thing, but we hardly ever see anyone using a RIGHT JOIN in a SQL script. Visually we can see everything gets reversed. Using a LEFT JOIN is also a lot easier to read since we know the driving table will always be in the FROM clause. With a LEFT JOIN we can think of it as a top-down approach where-as with RIGHT JOIN we can think of it as bottom-up. The SELECT doesn’t change and neither does the WHERE clause. RIGHT JOIN CUSTOMER C I ON I.CUSTOMERID = C.CUSTOMERID In our first example of finding all customers that do not have invoices we could re-write the SQL with a RIGHT JOIN like this: Regardless, we can still go over the inner workings of a RIGHT JOIN and how it can be used. In fact, if you’re using the DBeaver sample (SQLite) database, you’ll notice that RIGHT and FULL OUTER JOINs are not supported. Joining to a table with a RIGHT JOIN is not very common and this type of SQL outer join can be avoided by re-ordering a query and using LEFT JOIN instead. This is sometimes useful when we want to join to a filtered dataset and will be covered in another section since this is a bit more advanced. You may also notice that there is a sub-query or “query within a query”. If we ran the above query, we’d get results returned for all of the customers and any associated invoices tied to the latest month. Show me all customers and their associated invoices in the last month. WHERE strftime(‘%m’, INVOICEDATE) = strftime(‘%m’, CURRENT_DATE)ĪND strftime(‘%Y’, INVOICEDATE) = strftime(‘%Y’, CURRENT_DATE) In the second example, we could write the SQL script like this: We could also imagine the result set looking something like this: You’ll find that as you become more familiar with SQL, there are actually multiple ways to find certain answers and one is usually more efficient/performant than the other. This would indicate that there was no match found in the joined table. ![]() Since we want to know which customers DO NOT have invoices, we put in a WHERE condition to see if there are any NULL values in the invoice table after joining. This means we want all the results from Customer and the matching invoices based on the CustomerId. We then left join that table to Invoice on CustomerId. In this case the driving table would be Customer. Think of the table in the FROM clause as our main table or driving table. There is also no such thing as a LEFT INNER JOIN in case you were wondering. The first thing you may notice is that we did not include the OUTER keyword in the query. LEFT JOIN INVOICE I ON I.CUSTOMERID = C.CUSTOMERID How do we write a query with a LEFT OUTER JOIN?įor the first use case, we would write it like this: We still want to see the FULL customer list even if there are not invoices this month for that customer. Show me all our customers and their associated invoices in the last month.Maybe our boss asks us send a report to them showing all customers that never received an invoice. It’s possible that there are customers that have not yet been invoiced. We may want to know how many customers of at a company don’t have invoices.Why use a left table join? For several reasons: Just as the INNER keyword is options when using the JOIN operator, OUTER is also options when writing LEFT or RIGHT join in SQL. Get familiar, because you’ll write a lot of these if you’re being asked to write SQL scripts, views, or reports against a database. ![]() In fact, inner joins and left joins are going to be used 99% of the time we write SQL queries. There are very limited use cases for these types of joins. It’s possible we may come across another join type called a CROSS JOIN otherwise known as a cartesian or cartesian product. ![]() ![]() There are 3 types of outer joins the LEFT, RIGHT, and FULL OUTER JOIN. This is why we need to understand the SQL outer join. This operator will join two tables based on a condition and return the matching results, but that’s a really limited view on the types of questions we might need to ask from a database. In an earlier section we covered how to define a standard JOIN also known as an INNER JOIN.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |