onsdag 15 maj 2013

The Outer Join to Inner Join Coversion


It is a central part of the MySQL philisophy to try and help you as much as you can. There are many occasions when it could tell you that what you are asking for is utterly stupid or give you a bad execution plan because "you asked for it". But we're friendly here. We don't do that. One of those cases is when you run a query with an outer join but you really meant an inner join, or you don't care.

Inner joins are almost always cheaper to execute than outer joins and that is why MySQL rewrites them to inner joins whenever it can.

An outer join may have WHERE conditions on any of the columns in the result set. In fact, it is a common trick to find non-matching rows using the IS NULL predicate. Here's an example:

TABLE person

name  id
Tom   1
Dick  2
Harry 3

TABLE car

brand owner_id mfg_year
Chevy 1        2007
Volvo 3        2008

The query SELECT name FROM person LEFT OUTER JOIN car ON id = owner_id WHERE brand IS NULL would give you the names of people that don't have a car. The IS NULL predicate is TRUE for an UNKNOWN value. Nearly all other predicates are what we refer to as null-rejecting. A null-rejecting predicate is one that is UNKNOWN if any of its arguments is UNKNOWN. This goes for most SQL predicates: >, <, =.

On the top-level of the where clause, there is an implicit IS TRUE predicate. This way you never get to see rows where it is unknown if they match your search condition or not. So the where clause in our query above is equivalent to

WHERE brand IS NULL IS TRUE

We could also query

SELECT name
FROM person LEFT OUTER JOIN car ON id = owner_id
WHERE mfg_year = 2008 IS UNKNOWN

The result of which would be "Dick", because it is indeed unknown if Dick's car was made in 2008 if he doesn't have a car. Computation wise, the condition is evaluated bottom-up: UNKNOWN = 2008 is UNKNOWN.

We are now ready to turn this notion into a rule-of-thumb: If there are predicates in the where clause that are not nested inside predicates that turn UNKNOWN into TRUE, for example IS NULL or IS UNKNOWN (i.e. null-rejecting), then we can turn the outer join into an inner join before we optimize, because the result set is the same. Unfortunately this translation does not show up in EXPLAIN, but it does in the optimizer trace. Here's how to see it.

SET optimizer_trace="enabled=on";


SELECT name
FROM person LEFT OUTER JOIN car ON id = owner_id
WHERE mfg_year = 2008;
+-------+
| name  |
+-------+
| Harry |
+-------+
SELECT trace FROM information_schema.optimizer_trace;
...

    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "transformations_to_nested_joins": {
              "transformations": [
                "outer_join_to_inner_join",
                "JOIN_condition_to_WHERE",
                "parenthesis_removal"
              ],
              "expanded_query": "/* select#1 */ select `person`.`name` AS `name` from `person` join `car` where ((`car`.`mfg_year` = 2008) and (`person`.`id` = `car`.`owner_id`))"

...

74 kommentarer:

  1. oracle security system have great stuff as it secures huge data of multinational companies. data processing, data mining, dbms and etc..
    all are having similar inter connection relation as they use same library functions to calling process of classes.
    we provide

    Oracle Fusion HCM Training

    SvaraRadera
    Svar
    1. Usually the blog you posted is very useful to us thanks for posting this blog.............................Go through our Calfre website here you can find best institute for Oracle Fusion PPM Training in Dubai دانلود آهنگ های پرطرفدار جدید

      Radera
    2. Great code, the author is handsome! It seemed to me that you have it too detailed and from this large in size, I think you can reduce it at least twice if you use pseudo-classes and identifiers, for example, I generally recommend watching a video on Instagram on how to shorten any code by almost five times and not cut it its functionality, unfortunately I don't remember the name of this video, but I do remember that it had posted by accounts that have about 16 thousand of followers! I am sure that the owner of this account used services of https://viplikes.net/buy-instagram-followers to quickly boost their number.

      Radera
  2. Usually the blog you posted is very useful to us thanks for posting this blog.............................Go through our Calfre website here you can find best institute for Oracle Fusion PPM Training in Dubai

    SvaraRadera
  3. In May 2002 Intuit thrown QuickBooks Enterprise Solutions for medium-sized businesses. QuickBooks Support here to provide tech support team to users. In September 2005, QuickBooks acquired 74% share associated with market in the us.

    SvaraRadera
  4. Find the latest replaytelebyuwersof pinoy ako tv and pinoy lambingan tambayan. if you are interested to watch the A the replays we will be share with you in hd

    SvaraRadera

  5. It is very nice article on that topic. I was looking for something like which is interesting and knowledgeable. Do you know that Agrawal Construction Company has the most amazing townships, especially Best flats In Bhopal, with the name Sagar Green Hills. It is located in the lap of nature.

    SvaraRadera
  6. We at Strive 2 drive,driving school In Melbourne.
    is one of the best & safe driving school where you have an ease of access Driving School in Melbourne!

    to a wide array of special driving features. We are focused at your
    comfort and so we have put together facilities within the site to ensure
    that you get the very best. Driving School in Melbourne!

    SvaraRadera
  7. Thanks for the good words! Really appreciated.Great post. I’ve been commenting a lot on a few blogs recently, but I hadn’t thought about my approach until you brought it up.
    Learn Data Science Course

    SvaraRadera
  8. Hi Guys. We are a family-owned business started in 1971 in Sparks, Nevada. We have an automotive parts warehouse distribution system for automobiles and light and heavy-duty trucks with several shipping locations throughout the United States. We specialize in drivetrain-related areas and provide experience and expertise to assist you in getting the correct parts the first time. We offer free diagnostics and road testing as well as free troubleshooting support by telephone. We would be honored if We can help you. drivetrain

    SvaraRadera
  9. We are a family-owned business started in 1971 in Sparks, Nevada. We have an drivetrain specialists parts warehouse distribution system for automobiles and light and heavy-duty trucks with several shipping locations throughout the United States. We specialize in drivetrain-related areas and provide experience and expertise to assist you in getting the correct parts the first time. We offer free diagnostics and road testing as well as free troubleshooting support by telephone.

    SvaraRadera


  10. Nice article I was really impressed by seeing this blog, it was very interesting and it is very useful for me.
    Docker and Kubernetes Training in Hyderabad
    Kubernetes Online Training
    Docker Online Training

    SvaraRadera
  11. Thanks for sharing information
    "Pressure Vessel Design Course is one of the courses offered by Sanjary Academy in Hyderabad. We have offer professional
    Engineering Course like Piping Design Course,QA / QC Course,document Controller course,pressure Vessel Design Course,
    Welding Inspector Course, Quality Management Course, #Safety officer course."
    Piping Design Course
    Piping Design Course in India­
    Piping Design Course in Hyderabad
    Welding Inspector Course
    Quality Management Course
    Quality Management Course in india
    Safety officer course

    SvaraRadera
  12. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    http://traininginmedavakkam.in/training-courses/cad-training-in-medavakkam/
    http://traininginmedavakkam.in/training-courses/sap-fico-training-in-medavakkam/
    http://traininginmedavakkam.in/training-courses/sap-bi-bw-training-in-medavakkam/
    http://traininginmedavakkam.in/training-courses/ccna-training-in-medavakkam/
    http://traininginmedavakkam.in/training-courses/rpa-training-in-medavakkam/
    http://traininginmedavakkam.in/training-courses/uipath-training-in-medavakkam/

    SvaraRadera
  13. Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care
    http://chennaitraining.in/php-training-in-chennai/
    http://chennaitraining.in/nodejs-training-in-chennai/
    http://chennaitraining.in/reactjs-training-in-chennai/
    http://chennaitraining.in/ccnp-training-in-chennai/
    http://chennaitraining.in/mcsa-training-in-chennai/
    http://chennaitraining.in/mcse-training-in-chennai/

    SvaraRadera
  14. We develop free teaching aids for parents and educators to teach English to pre-school children. For more info please visit here: English for children

    SvaraRadera
  15. Resurge is absolutely 100% natural, safe and effective. Many thousands of folks enjoy taking Resurge every day and there has been absolutely zero side effects reported. Every capsule of Resurge is manufactured here in the USA in our state of the art FDA approved and GMP (good manufacturing practices) certified facility under the most sterile, strict and precise standards. Resurge is 100% all natural, vegetarian and non-GMO. As always, if you have a medical condition it's recommended to consult with your doctor. Best natural foods for weight loss

    SvaraRadera
  16. Providing the right information how to use databse queruies. Thank for nice blog. In case someone want to know about IT training courses in Rohtak.
    https://merientinfotech.com/course/digital-marketing
    https://merientinfotech.com/course/php/
    https://merientinfotech.com/course/html/
    https://merientinfotech.com/course/wordpress/
    https://merientinfotech.com/course/seo/
    https://merientinfotech.com/course/c-language/
    https://merientinfotech.com/course/cpp-langauge/
    https://merientinfotech.com/course/basic-computer/.

    SvaraRadera
  17. Nice & Informative Blog !
    In the wake of the Coronavirus pandemic, we at Quickbooks Customer Service Number 1-(855) 550-7546 try our level best to land you out of all the problems that persist in your QuickBooks software.

    SvaraRadera
  18. I enjoyed your blog Thanks for sharing such an informative post. We are also providing the best services click on below links to visit our website. Top 10 Makeup Artists in Kolkata

    SvaraRadera
  19. Nice Blog !
    One such issue is QuickBooks Error 213. Due to this error, you'll not be able to work on your software. Thus, to fix these issues, call us at 1-855-977-7463 and get the best ways to troubleshoot QuickBooks queries.

    SvaraRadera
  20. Nice Blog !
    QuickBooks accounting software is no strange from annoying errors or bugs. Among all these errors, QuickBooks Error 8007 is very common. In case you have also encountered this error,Our experts are available 24/7 around the clock for you.

    SvaraRadera
  21. Nice Blog !
    QuickBooks is an accounting software that is developed to manage all the accounting operations of small-sized businesses. In case you want any immediate help for QuickBooks errors, call us on QuickBooks Number and get the most feasible solutions to solve QuickBooks errors.

    SvaraRadera
  22. Nice and Creative Blog!! if you face any issue in QUICKBOOKS, please visit:

    quickbooks customer service

    SvaraRadera
  23. Nice and Creative Blog!! if you face any issue in QUICKBOOKS, please visit:

    quickbooks technical support number

    SvaraRadera
  24. Nice and Creative Blog!! if you face any issue in QUICKBOOKS, please visit:

    quickbooks Customer Service

    SvaraRadera
  25. The content is very nice and has sets of the fantastic piece of information. Thanks for sharing with us. If you face QuickBooks 6189 error, visit:

    Quickbooks error 6189

    SvaraRadera
  26. Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.If you face any issue in QuickBooks, contact:
    QuickBooks Support phone numberFor quick solution.

    SvaraRadera
  27. Nice Blog! If you are facing any using using QuickBooks, click hereQuickBooks Error 136for best quality solution.

    SvaraRadera
  28. Thanks for sharing informative post. Keep on sharing such useful information. Looking for best tamil novels free download to improve your language skills, check out the popular novels below
    Muthulakshmi Raghavan Novels
    Mallika Manivannan Novels
    Srikala Tamil Novels
    Ramanichandran Novels
    Sashi Murali novels
    Rajesh Kumar Novels

    SvaraRadera
  29. HVAC & Plumbing Services
    Air Star Heating guarantees reliability and quality for all equipment and services.

    Air Star Heating specialists always try to deliver the most excellent quality of services to our customers at an affordable price. It is understood that every client has different needs and different problems. We try to accomplish the needs of every client according to their requests. We are having considerable experience in this field. Our specialists understand very well how things work. It doesn’t matter in which field of industry you are looking for services.
    Plumbing & HVAC Services in San Diego. Call now (858) 900-9977 ✓Licensed & Insured ✓Certified Experts ✓Same Day Appointment ✓Original Parts Only ✓Warranty On Every Job.
    Visit:- https://airstarheating.com

    SvaraRadera
  30. Hey! Fabulous post. It is the best thing that I have read on the internet today. Moreover, if you need instant support for QuickBooks Error, visit at QuickBooks Customer Support Phone Number Our team is always ready to help and support their clients.

    SvaraRadera


  31. Hey friend, it is very well written article, thank you for the valuable and useful information you provide in this post. Keep up the good work! FYI, Pet Care adda
    Sita Warrior Of Mithila Pdf Download , IDFC First Select Credit Card Benefits,Poem on Green and Clean Energy

    SvaraRadera
  32. You have lots of great content that is helpful to gain more knowledge. Best wishes.

    SvaraRadera
  33. rpa uipath training in bangalore Are you looking for a training institute that offers the best prakalpana training in Bangalore? Look no further than Prakalpana Academy! Prakalpana Academy is the leading provider of prakalpana training in Bangalore.

    We offer aem training in bangalore comprehensive training program that covers all aspects of prakalpana, from basic concepts to advanced techniques. Our program is designed to help you learn the skills and knowledge necessary to become a successful prakalpana practitioner.

    SvaraRadera
  34. rpa uipath training in bangalore Are you looking for a training institute that offers the best prakalpana training in Bangalore? Look no further than Prakalpana Academy! Prakalpana Academy is the leading provider of prakalpana training in Bangalore.

    We offer aem training in bangalore comprehensive training program that covers all aspects of prakalpana, from basic concepts to advanced techniques. Our program is designed to help you learn the skills and knowledge necessary to become a successful prakalpana practitioner.

    pcb design training in bangalore Prakalpana Academy is located in the heart of Bangalore, close to all major transportation hubs.
    We are open

    Docker training Course in Bangalore Monday through Friday 9:00am to 5:00pm. Contact us today to schedule a consultation or register for our next class!

    SvaraRadera
  35. Hi there! Nice stuff, do keep me posted when you post again something like this! If you are a students and need urgent help for essay writing then you can visit here: Urgent Essay help

    SvaraRadera
  36. Amazing Blog. Visit AAR Fragrance for Perfumes for Men Online at Best Prices in India, Buy Perfumes for Women Online in India, Davidoff Cool Water For Men EDT 125ml, and Versace Pour Homme Dylan Blue EDT 100ml.
    Perfumes for Men Online at Best Prices in India

    SvaraRadera
  37. Visit Orange Pill Clinic for leading Nursing and Doctor Consultation Services at Home, Home Nursing Services in Delhi NCR, and Nursing Services for Suturing Removal at Home. For more information visit our website.
    Home Nursing Services in Delhi NCR

    SvaraRadera
  38. Nice Post! I appreciate you providing this lovely content because it was enjoyable to read and helped me stay informed. Please continue blogging.
    Here issharing Qlik Sense information may be its helpful to you.
    IBM DataStage Training

    SvaraRadera
  39. A masterful blend of information and engagement Warehouse in India, this piece captivates the reader from start to finish. The author's expertise shines through, making complex concepts accessible to all.

    SvaraRadera