The second way to find non-matching records between tables is to use NOT in conjunction with the IN operator. Running the above statement confirms that Mary Parker is the only employee who has not received a bonus. Plugging in our data produces the following Select query: One way to select values present in one table but missing in another is to use a combination of a Left Join with an “IS NULL” test. What we want to see is a list of names of employees’ IDs that do not appear in the bonuses table. While there is only one instance of each employee in the employees table, there are potentially multiple instances of each in the bonuses table, depending on how many bonuses he or she has received. Here are the two sample tables that we’ll execute our queries against: I will be presenting each here, along with another that you might not have considered. MySQL provides not one but three standard means of producing non-matching results. This is commonly referred to as a Non-matching query, although it is sometimes called a Subtract or Set Difference query as well. To provide the requestor with the statistics, you’ll have to determine which sales people are absent from the bonuses table. For instance, it would be instructive for management to identify sales people who haven’t received any bonuses, as these might be deemed to be the underperformers. There are many times as a database developer and administrator that you’ll want to know what entities are being excluded from a straight table join.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |