go backarticles

Articles of SQLschool.gr Team

Filtering data on joins or to where clause?

Antonios Chatzipavlis

Ένα ερώτημα που όλους κάποια στιγμή έχει απασχολήσει και έχει δημιουργήσει ενδιαφέρουσες συζητήσεις είναι το που είναι καλύτερο να βάλω τα φίλτρα μου σε ένα join query, να τα βάλω στο where όπως θα έκανα ή να τα βάλω στο join;

Αν και πίστευα ότι είχε ήδη απαντηθεί σαν ερώτημα, εντούτοις μια ερώτηση από ένα μαθητή μου στο πρόσφατο σεμινάριο που έκανα έφερε πάλι στην επιφάνεια το ερώτημα αυτό. Για να πάρουμε όμως τα πράγματα με μια σειρά με την ελπίδα να λύσουμε το γρίφο γύρω από αυτό το θέμα.

Αρχικά θα πρέπει να γνωρίζουμε πως εκτελείτε ένα query. Έχω γράψει για αυτό αρκετά χρόνια πριν σε αυτό το post. Το δεύτερο που πρέπει να καταλάβουμε είναι η SQL μια declarative γλώσσα και αυτό που κάνουμε με αυτή είναι να πούμε τι θέλουμε και όχι πώς να γίνει η εκτέλεση σε βήματα για να πάρουμε αυτό που θέλουμε.

Inner Joins

Αρχικά ας πάρουμε ένα απλό inner join query μεταξύ πελατών και παραγγελιών από την γνωστή σε όλους Northwind database. Σε αυτή υπάρχουν 91 πελάτες και 830 παραγγελίες και μόνο 2 πελάτες δεν έχουν παραγγελίες

Το query μας είναι το παρακάτω

select * from Customers as c
inner join orders as o on c.CustomerID=o.CustomerID

Αυτό λοιπόν το query για να εκτελεστεί κα να πάρουμε τα αποτέλεσμα μας θα κάνει πρώτα το καρτεσιανό γινόμενο των δύο πινάκων και σε αυτό επάνω θα εφαρμόσει το expression που έχουμε ορίσει στον on clause του join.

Αν σε αυτό προσθέσουμε ένα where όπως για παράδειγμα

select * from Customers as c
inner join orders as o on c.CustomerID=o.CustomerID
where c.City='london'

Τότε απλά μετά τα βήματα που περιέγραψα πριν θα έρθει και θα εφαρμόσει το expression που έχω στο where clause.

Αρκετοί είναι αυτοί που πιστεύουν ότι μπορώ να κάνω αυτό το query να πάει καλύτερα και έτσι γράφουν το παραπάνω  ερώτημα ως εξής

select * from Customers as c
inner join orders as o on c.CustomerID=o.CustomerID and c.City='london'

Και το ένα και το άλλο φέρνουν το ίδιο αποτέλεσμα και μάλιστα έχουν και ακριβώς το ίδιο execution plan, ακόμα και στα ποσοστά του κόστους συνολικά αλλά στα επιμέρους σημεία.
Άρα όσο και να προσπαθείτε να ξεγελάσετε τον Query Optimizer δεν θα το καταφέρετε. Φυσικά ακούω στα αυτιά μου κάποιες φωνές που να λένε ότι δεν είναι σωστό αυτό και ότι το κάνει λάθος ο optimizer, τώρα αν αυτό που απορρέει από την σχεσιακή άλγεβρα είναι λάθος θα το αφήσω στην κρίση του κάθε ενός.

Με απλά λόγια όπου και να βάλετε τα φίλτρα σε inner join δεν έχει καμία σημασία, απλά θα κάνω μια μικρή υποσημείωση ότι έχοντας τα φίλτρα στο where υπάρχει μια μικρή διαφορά απόδοσης προς το καλύτερο.

Outer Joins

Τα πράγματα όμως περιπλέκονται όταν αντί για inner join έχω οuter joins (left, right, full) εκεί έχει σημασία που θα ορίσω τα φίλτρα και μάλιστα θα έχω και διαφορετικό αποτέλεσμα. Αυτό που έχω παρατηρήσει είναι ότι υπάρχουν πολλές παρανοήσεις γύρω από τα outer joins με αποτέλεσμα αυτά να χρησιμοποιούνται με λάθος τρόπο.

Ας πάρουμε για παράδειγμα το παρακάτω query

select * from Customers as c
left outer join orders as o on c.CustomerID=o.CustomerID

Πως θα εκτελεστεί αυτό;
Αρχικά θα εκτελεστεί το FROM όπου επειδή έχω δύο πίνακες θα φτιάξει το καρτεσιανό τους (VT1 - virtual table). Έπειτα στο VT1 θα εφαρμόσει το ΟΝ και ότι predicates έχω βάλει σε αυτό και που γίνονται true έτσι θα φτιάξει το VT2. Επειδή όμως έχω outer join (left στην περίπτωση μας) θα πρέπει να συμπεριληφθούν και τα records που δεν κάνουν match με στο ON VT3. Έτσι στο παράδειγμα μας θα έχουμε όλους τους πελάτες έχουν δεν έχουν παραγγελίες και στα πεδία της παραγγελίας όσοι δεν έχουν παραγγελία θα υπάρχει null. Αυτό φυσικά που γράφω δεν είναι κάτι νέο και όλοι το γνωρίζουν.

Αν στο query αυτό κάνω προσθέσω ένα φίλτρο με where όπως παρακάτω

select * from Customers as c
left outer join orders as o on c.CustomerID=o.CustomerID
where c.City='london'

Τότε στα παραπάνω βήματα θα προστεθεί  ακόμα ένα όπου στο VT3 θα εφαρμοστεί το predication που έχω στο where και έτσι θα πάρω σαν αποτέλεσμα 46 γραμμές με τους πελάτες και τις παραγγελίες τους που είναι από το Λονδίνο. Αυτό είναι και το αναμενόμενο σαν αποτέλεσμα από όλους σωστά;
 
Αν όμως είχα γράψει το query με διαφορετικό τρόπο όπως παρακάτω

select * from Customers as c
left outer join orders as o on c.CustomerID=o.CustomerID and c.City='london'

Το αποτέλεσμα που θα πάρω είναι 131 γραμμές και το οποίο θα περιέχει όλους τους πελάτες αλλά μόνο για αυτούς που είναι από το Λονδίνο θα δείχνει της παραγγελίες τους. Πώς έγινε αυτό;

Ας παίξουμε λίγο την διαφήμιση με το τύπο που είναι στον έλεγχο αποσκευών στο αεροδρόμιο και ζητάει να κάνουν ζουμ στο κινητό του για να αναφωνήσει "αχα το δεξί καλώδιο έχει πετσικάρει", έτσι θα κάνουμε και εμείς ζουμ χωρίς να του βγάλουμε το ζουμί.

Αρχικά θα γίνει, όπως πάντα το καρτεσιανό γινόμενο για να δείτε το αποτέλεσμα αυτού απλά εκτελέστε το παρακάτω query

select * from Customers
cross join Orders

Αυτό θα σας επιστρέψει 75530 γραμμές (VT1). Σε αυτό θα εφαρμοστεί το ΟΝ και αυτό σημαίνει ότι από τους 91 πελάτες οι 6 είναι από το Λονδίνο και αυτοί έχουν 46 παραγγελίες (VT2) μπορείτε να δείτε το αποτέλεσμα με το παρακάτω query

select * from
( select c.CustomerID,c.City,o.OrderID,o.CustomerID as oCustomerID from Customers as C
  cross join Orders as o) as t
  where customerid=oCustomerID and City='london'

Επειδή όμως είναι οuter join όπως έχουμε ήδη πει θα προστεθούν οι γραμμές που λείπουν από τον πίνακα που δεν εκπληρώνουν τα αναφερόμενα στο ΟΝ του outer join στην περίπτωση μας είναι οι πελάτες και  αυτοί είναι 86 (VT3) άρα 131 γραμμές. Με αυτό νομίζω ότι φτάσαμε στο σημείο να ανακαλύψουμε ότι το καλώδιο έχει πετσικάρει.

Αυτό φυσικά δεν συμβαίνει όταν έχω τα φίλτρα μου στο where όπως είδαμε και παραπάνω.

Μετά από όλα αυτά ελπίζω να έγινε κατανοητό το που πρέπει να μπαίνουν τα φίλτρα μας σε ένα query.

Για όσους δεν τα κατάλαβαν ας ξαναδιαβάσουν το post αλλά where is the answer ;)

Keep Rocking
/*antonch*/


 


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Comments

user-gravatar

On 12 Feb 2014 @ 2:27 PM The Questioner wrote:

Ερώτηση κρίσεως (με δεδομένο οτι δεν απευθύνομαι σε νεαρούς μαθητές αλλά σε επαγγελματίες): Προς τι όλη αυτή η θεωρία περι σχεσιακής άλγεβρας και λοιπών "υποθέσεων" περί του query opt, όταν το τι παίζει καλύτερα (και κατά συνέπεια τον τρόπο που δουλεύει ο qo) μπορώ να το δω με ένα απλό show execution plan?

user-gravatar

On 09 Mar 2014 @ 1:56 AM Antonios Chatzipavlis wrote:

Μήπως γιατί ο optimizer ένα από αυτά που χρησιμοποιεί είναι η σχεσιακή άλγεβρα;

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.