It does not filter out all of the rows that didn’t have facebook.city = ‘SF’ Optimization The LEFT JOIN brings in every row and the data that is JOINed in from linkedin only happens when facebook.name = linkedin.name AND facebook.city = ‘SF’. The join condition is different in this query. SELECT * FROM facebook LEFT JOIN linkedin ON facebook. Let’s evaluate how readable each option is, these two queries will produce the same output: This time we are looking for which people are both our friends and connections, but we only want to see the one(s) who also live in SF. ![]() Let’s use a slightly larger data set to demonstrate this. There are readability and accuracy concerns to address with filtering in the ON clause. Filtering dataīoth the ON and WHERE clause can be used to filter data in a query. Be careful of caching affecting the results of your queries. However the way query plans are created may vary across SQL languages and versions, again in this instance it should all be the same but you can test it out on your Database to see if you get anymore performance. Using WHERE or ON to JOIN the data should produce the same query plan. It takes the query and then creates an optimized way to find the data. A query plan is the code that SQL comes up with to execute the query. However in this case there should be no speed benefits because of something called a query plan. Sometimes writing a query in a different way can yield speed improvements. city = "SF"Įven though the first query has fewer characters than the second it is not as easily understood. city = "SF" ) SELECT * FROM facebook JOIN linkedin ON facebook. SELECT * FROM facebook, linkedin WHERE facebook. So when more filtering conditions are added to the WHERE clause in addition to using it to define how to JOIN the data it becomes harder to understand. It is most often used to filter the data. JOINing in the WHERE clause can be confusion since this is not it’s typical purpose. If you want to use a JOIN other than an INNER JOIN stating it explicitly makes it clear what is going on. In most cases implicit JOINs will act as INNER JOINs. The last query is using what is called an implicit JOIN(a JOIN that is not explicitly stated in the query. In the last query we have to look closely to both establish what table are being JOINed and how they are being JOINed. In the second query it seems just as clear however we may do a double take on the WHERE clause since this is typically used to filter data and not JOIN it. We can also clearly see the join condition in the ON clause. In the first query we can easily see the tables being joined in the FROM and JOIN clause. The main difference between these queries is how easy it is to understand what is going on. ![]() An Implicit JOIN does not specify the JOIN type and use the WHERE clause to define the join condition. An explicit JOIN explicitly tells you how to JOIN the data by specifying the type of JOIN and the join condition in the ON clause. The first two are types of explicit joins and the last is an implicit join. ![]() ![]() name SELECT * FROM facebook, linkedin WHERE facebook. name SELECT * FROM facebook JOIN linkedin WHERE facebook. SELECT * FROM facebook JOIN linkedin ON facebook. Lets now query using a variety of defining the JOIN condition.Īll three of these queries produce the same correct result: We want to see the people who are both our friend and our connection. To demonstrate this, lets use an example data set of facebook friends and linkedin connections. The way both of these clauses can be used to help join data is through defining the condition on which the two tables are joined. To go more in depth we will cover the two use cases that either WHERE or ON can support: The splitting of these purposes with their respective clauses makes the query the most readable, it also prevents incorrect data being retrieved when using JOINs types other than INNER JOIN. I used the word should because this is not a hard rule. ON should be used to define the join condition and WHERE should be used to filter the data. Last modified: DecemIs there a difference between the WHERE and ON clause?
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |