Ψευδώνυμα (Aliases) Στηλών και Πινάκων

Μερικές φορές είναι βολικό να αναφερόμαστε στις στήλες και στους πίνακες της MySQL με διαφορετικά ονόματα. Θα δούμε ένα παράδειγμα μιας βάσης δεδομένων που χρησιμοποιείται στο σύστημα κρατήσεων online μιας αεροπορικής εταιρείας. Για να παρουσιάσει τις πτήσεις που προσφέρονται από την εταιρεία, η βάση δεδομένων περιέχει τους εξής δύο πίνακες : Flights και Cities.
Η κάθε καταχώρηση στον πίνακα Flights παριστάνει μια πραγματική πτήση ανάμεσα σε δύο πόλεις, την αρχή και τον προορισμό της πτήσης. Προφανώς, οι Origin και Destination θα αποτελούν στήλες του πίνακα Flights, μαζί μ’ άλλες στήλες για πράγματα όπως η ημερομηνία και η ώρα της πτήσης, ο τύπος του αεροσκάφους, ο αριθμός της πτήσης και τα διάφορα ναύλα.
Ο πίνακας Cities περιέχει μια λίστα όλων των πόλεων προς τις οποίες πετάει η εταιρεία. Έτσι, και οι δύο στήλες Origin και Destination στον πίνακα Flights θα πρέπει να περιέχουν κωδικούς (ID’s) για να αναφέρονται στις καταχωρήσεις του πίνακα Cities. Θα δούμε τώρα μερικά ερωτήματα (queries).
Για να πάρουμε μια λίστα όλων των πτήσεων με τις πόλεις προέλευσής τους :

mysql> SELECT Flights.Number, Cities.Name
-> FROM Flights, Cities
-> WHERE Flights.Origin = Cities.ID;
+--------+-----------+
| Number | Name      |
+--------+-----------+
| CP110  | Montreal  |
| CP226  | Sydney    |
| QF2026 | Melbourne |
...      ...

Για να πάρουμε μια λίστα όλων των πτήσεων με τις πόλεις προορισμού τους :

mysql> SELECT Flights.Number, Cities.Name
-> FROM Flights, Cities
-> WHERE Flights.Destination = Cities.ID;
+--------+----------+
| Number | Name     |
+--------+----------+
| CP110  | Sydney   |
| CP226  | Montreal |
| QF2026 | Sydney   |
...      ...

Τι μπορούμε να κάνουμε τώρα αν θέλουμε να εμφανίσουμε την προέλευση και τον προορισμό της κάθε πτήσης μ’ ένα μόνο ερώτημα; Ένα ερώτημα που μπορεί να σκεφθούμε να χρησιμοποιήσουμε είναι το εξής :

mysql> SELECT Flights.Number, Cities.Name, Cities.Name
-> FROM Flights, Cities
-> WHERE Flights.Origin = Cities.ID
-> AND Flights.Destination = Cities.ID;
Empty set (0.01 sec)

Το παραπάνω ερώτημα δεν δουλεύει επειδή λέμε στην MySQL να ενώσει (join) τους πίνακες Flights και Cities και να εμφανίσει τον αριθμό πτήσης, το όνομα της πόλης και το όνομα της πόλης ξανά όλων των καταχωρήσεων που επιστρέφονται αν ταιριάξουμε το Origin με το City ID και το Destination με το City ID.
Μ’ άλλα λόγια, οι στήλες Origin, Destination και City ID πρέπει να είναι ίσες μεταξύ τους. Αυτό έχει σαν αποτέλεσμα μια λίστα όλων των πτήσεων όπου η προέλευση και ο προορισμός είναι ίδιοι. Δεν θα υπάρχουν λοιπόν καθόλου καταχωρήσεις που να ταιριάζουν με την παραπάνω περιγραφή και έτσι είχαμε το αποτέλεσμα “Empty set”.
Αυτό που χρειαζόμαστε είναι ένας τρόπος να μπορούμε να επιστρέψουμε δύο διαφορετικές καταχωρήσεις από τον πίνακα Cities, μια για την προέλευση και μια για τον προορισμό, για κάθε αποτέλεσμα. Αν είχαμε δύο αντίγραφα του πίνακα, ένα με όνομα Origins και ένα με όνομα Destinations, αυτό θα ήταν πολύ εύκολο να γίνει, αλλά γιατί να έχουμε δύο πίνακες που να περιέχουν την ίδια ακριβώς λίστα των πόλεων;
Η λύση είναι να δώσουμε στον πίνακα Cities δύο διαφορετικά προσωρινά ονόματα (ψευδώνυμα, aliases) για τους σκοπούς αυτού του ερωτήματος (query). Αν γράψουμε μετά από το όνομα ενός πίνακα την έκφραση AS Alias στο τμήμα FROM του ερωτήματος SELECT, μπορούμε να του δώσουμε ένα προσωρινό όνομα με το οποίο να αναφερόμαστε σ’ αυτό οπουδήποτε στο ερώτημα. Ακολουθεί το παραπάνω ερώτημα ξανά, αλλά αυτή τη φορά έχουμε δώσει στον πίνακα Cities το ψευδώνυμο (alias) Origins.

mysql> SELECT Flights.Number, Origins.Name
-> FROM Flights, Cities AS Origins
-> WHERE Flights.Origin = Origins.ID;

Αναφερόμενοι στον πίνακα Cities δύο φορές, χρησιμοποιώντας δύο διαφορετικά ψευδώνυμα (aliases), μπορούμε να χρησιμοποιήσουμε μια ένωση (join) τριών πινάκων, όπου οι δύο από τους πίνακες θα είναι ουσιαστικά ίδιοι, για να πάρουμε το αποτέλεσμα που θέλουμε :

mysql> SELECT Flights.Number, Origins.Name,
->        Destinations.Name
-> FROM Flights, Cities AS Origins,
->      Cities AS Destinations
-> WHERE Flights.Origin = Origins.ID
-> AND Flights.Destination = Destinations.ID;
+--------+-----------+----------+
| Number | Name      | Name     |
+--------+-----------+----------+
| CP110  | Montreal  | Sydney   |
| CP226  | Sydney    | Montreal |
| QF2026 | Melbourne | Sydney   |
...      ...         ...

Μπορούμε επίσης να ορίσουμε ψευδώνυμα για τα ονόματα στηλών. Θα μπορούμε να το χρησιμοποιήσουμε αυτό, για παράδειγμα, για να διαχωρίσουμε τις δύο στήλες “Name” στον παραπάνω πίνακα αποτελεσμάτων :

mysql> SELECT F.Number, O.Name AS Origin,
->        D.Name AS Destination
-> FROM Flights AS F, Cities AS O, Cities AS D
-> WHERE F.Origin = O.ID AND F.Destination = D.ID;
+--------+-----------+-------------+
| Number | Origin    | Destination |
+--------+-----------+-------------+
| CP110  | Montreal  | Sydney      |
| CP226  | Sydney    | Montreal    |
| QF2026 | Melbourne | Sydney      |
...      ...         ...

Η Έκφραση GROUP BY

Στα προηγούμενα, είχαμε δει το παρακάτω query, το οποίο μας εμφανίζει πόσα jokes είναι αποθηκευμένα στον πίνακα Jokes :

mysql> SELECT COUNT(*) FROM Jokes;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+

Η συνάρτηση COUNT() της MySQL ανήκει σε μια ειδική κατηγορία συναρτήσεων άθροισης (summary functions) ή συναρτήσεων ομαδοποίησης (group-by functions). Σ’ αντίθεση μ’ άλλες συναρτήσεις οι οποίες επηρεάζουν τις καταχωρήσεις στο αποτέλεσμα του ερωτήματος SELECT, οι συναρτήσεις άθροισης ομαδοποιούν μαζί όλα τα αποτελέσματα και επιστρέφουν ένα μόνο αποτέλεσμα.
Στο παραπάνω παράδειγμα, η συνάρτηση COUNT() επιστρέφει τον συνολικό αριθμό των γραμμών του αποτελέσματος.
Ας υποθέσουμε ότι θέλουμε να εμφανίσουμε μια λίστα των συγγραφέων με τον αριθμό των jokes που αντιστοιχεί στον καθένα. Η πρώτη μας σκέψη θα ήταν να πάρουμε μια λίστα των ονομάτων και των ID’s όλων των συγγραφέων και μετά να χρησιμοποιήσουμε τη συνάρτηση COUNT() για να μετρήσουμε τον αριθμό των αποτελεσμάτων όταν κάνουμε SELECT στα jokes με το ID του κάθε συγγραφέα. Ο PHP κώδικας θα είναι ως εξής :

// Μια λίστα όλων των συγγραφέων
$authors = mysql_query( "SELECT Name, ID FROM Authors" );
// Επεξεργασία του κάθε συγγραφέα
while ($author = mysql_fetch_array($authors)) {
$name = $author["Name"];
$id = $author["ID"];
// Μέτρηση των jokes που ανήκουν σ' αυτόν τον συγγραφέα
$result = mysql_query(
"SELECT COUNT(*) AS NumJokes ".
"FROM Jokes WHERE AID=$id" );
$row = mysql_fetch_array($result);
$numjokes = $row["NumJokes"];
// Εμφάνιση του συγγραφέα και του αριθμού των jokes
echo("<P>$name ($numjokes jokes)</P>");
}

Χρησιμοποιήσαμε το AS στο δεύτερο query παραπάνω για να δώσουμε ένα φιλικότερο όνομα (NumJokes) στο αποτέλεσμα της COUNT(*). Η τεχνική αυτή θα δουλέψει αλλά θα χρειασθεί n+1 ξεχωριστά ερωτήματα, όπου n είναι ο αριθμός των συγγραφέων στη βάση δεδομένων. Υπάρχει, όμως, μια λύση σ’ αυτό.
Προσθέτοντας την έκφραση GROUP BY σ’ ένα ερώτημα SELECT, μπορούμε να πούμε στην MySQL να ομαδοποιήσει (group) τα αποτελέσματα του ερωτήματος σε σύνολα που να έχουν την ίδια τιμή στις στήλες που έχουμε καθορίσει. Οι συναρτήσεις άθροισης, όπως είναι η COUNT(), επενεργούν μετά σ’ αυτές τις ομάδες και όχι στο συνολικό αποτέλεσμα.
Το ακόλουθο query, για παράδειγμα, εμφανίζει τον αριθμό των jokes που έχουν αποδοθεί σε κάθε συγγραφέα στη βάση δεδομένων :

mysql> SELECT Authors.Name, COUNT(*) AS NumJokes
-> FROM Jokes, Authors
-> WHERE AID = Authors.ID
-> GROUP BY AID;
+-----------------+----------+
| Name            | NumJokes |
+-----------------+----------+
| Kevin Yank      |        3 |
| Joan Smith      |        1 |
| Ted E. Bear     |        5 |
+-----------------+----------+

Ομαδοποιώντας τα αποτελέσματα ως προς το ID του συγγραφέα (AID), παίρνουμε μια ταξινόμηση των αποτελεσμάτων για τον κάθε συγγραφέα. Θα μπορούσαμε να είχαμε χρησιμοποιήσει και την έκφραση GROUP BY Authors.ID και να πάρουμε το ίδιο αποτέλεσμα.

Η Έκφραση LEFT JOIN

Μπορούμε να δούμε από τα παραπάνω αποτελέσματα ότι ο Kevin Yank έχει τρία jokes στο όνομά του, ο Joan Smith έχει ένα και ο Ted E. Bear έχει πέντε. Αυτό που δεν δείχνουν αυτά τα αποτελέσματα είναι ότι υπάρχει ένας τέταρτος συγγραφέας, η Amy Mathieson, που δεν έχει κανένα joke στο όνομά της. Εφόσον δεν υπάρχουν καταχωρήσεις στον πίνακα Jokes με AID’s που να ταιριάζει το δικό της ID, δεν θα υπάρχουν αποτελέσματα που να ικανοποιούν την έκφραση WHERE στο παραπάνω ερώτημα γι’ αυτήν και συνεπώς θα αποκλειστεί από τον πίνακα των αποτελεσμάτων.
Η MySQL παρέχει μια άλλη μέθοδο για την ένωση (joining) πινάκων, δηλ. την εμφάνιση πληροφοριών από πολλούς πίνακες ταυτόχρονα, που απoκαλείται left join, και είναι σχεδιασμένη γι’ αυτήν ακριβώς την κατάσταση. Για να κατανοήσουμε πώς διαφέρουν τα left joins από τα standard joins, πρέπει πρώτα να θυμηθούμε πώς δουλεύουν τα standard joins.
Η MySQL εκτελεί ένα standard join δύο πινάκων εμφανίζοντας όλους τους δυνατούς συνδυασμούς των γραμμών αυτών των πινάκων. Σε μια απλή περίπτωση, ένα standard join δύο πινάκων με δύο γραμμές για τον καθένα θα περιέχει τέσσερις γραμμές. Αφού υπολογισθούν οι γραμμές του αποτελέσματος, η MySQL μετά κοιτάει την έκφραση WHERE για να πάρει οδηγίες για το ποιες γραμμές πρέπει να εμφανισθούν, όπως για παράδειγμα εκείνες που η στήλη AID του πίνακα 1 θα ταιριάζει με τη στήλη ID του πίνακα 2.
Ο λόγος που το παραπάνω δεν ικανοποιεί τους σκοπούς μας είναι ότι θα θέλαμε να συμπεριλάβουμε επίσης και τις γραμμές του πίνακα 1 (Authors) που δεν έχουν κάποιες γραμμές που να ταιριάζουν στον πίνακα 2 (Jokes). Ένα left join κάνει ό,τι ακριβώς χρειαζόμαστε, αναγκάζοντας μια γραμμή να εμφανισθεί στα αποτελέσματα για κάθε γραμμή του πρώτου (αριστερού) πίνακα, ακόμη κι αν δεν βρεθούν καταχωρήσεις που να ταιριάζουν στον δεύτερο (δεξιό) πίνακα. Αυτές οι καταχωρήσεις παίρνουν τιμές NULL γι’ όλες τις στήλες του δεξιού πίνακα.
Για να κάνουμε ένα left join ανάμεσα σε δύο πίνακες στην MySQL, διαχωρίζουμε τα ονόματα των δύο πινάκων στην έκφραση FROM με το LEFT JOIN αντί με κόμμα. Γράφουμε μετά το όνομα του δεύτερου πίνακα με ON <συνθήκη>, όπου η <συνθήκη> καθορίζει τα κριτήρια ταιριάσματος των γραμμών στους δύο πίνακες. Ακολουθεί το αναθεωρημένο ερώτημα για την εμφάνιση των συγγραφέων με τον αριθμό των jokes που τους έχει αποδοθεί :

mysql> SELECT Authors.Name, COUNT(*) AS NumJokes
-> FROM Authors LEFT JOIN Jokes
-> ON AID = Authors.ID
-> GROUP BY AID;
+---------------+----------+
| Name          | NumJokes |
+---------------+----------+
| Amy Mathieson |        1 |
| Kevin Yank    |        3 |
| Joan Smith    |        1 |
| Ted E. Bear   |        5 |
+---------------+----------+

Πρέπει να έχουμε υπόψη μας ότι η συνάρτηση COUNT(*) μετράει τον αριθμό των γραμμών που επιστρέφονται για κάθε συγγραφέα. Αν κοιτάξουμε στα μη ομαδοποιημένα αποτελέσματα του LEFT JOIN, θα δούμε τα εξής :

mysql> SELECT Authors.Name, Jokes.ID AS JokeID
-> FROM Authors LEFT JOIN Jokes
-> ON AID = Authors.ID;
+---------------+--------+
| Name          | JokeID |
+---------------+--------+
| Kevin Yank    |      1 |
| Kevin Yank    |      2 |
| Kevin Yank    |      4 |
| Joan Smith    |      3 |
| Ted E. Bear   |      5 |
| Ted E. Bear   |      6 |
| Ted E. Bear   |      7 |
| Ted E. Bear   |      8 |
| Ted E. Bear   |      9 |
| Amy Mathieson |   NULL |
+---------------+--------+

Τώρα η Amy Mathieson έχει μια γραμμή με τιμή NULL. Το ότι η τιμή του Joke ID είναι NULL δεν επηρεάζει την συνάρτηση COUNT(*) αυτή το μετράει σαν μια γραμμή. Αν αντί για το *, προσδιορίσουμε το όνομα μιας στήλης, όπως Jokes.ID, θα αγνοήσει τις τιμές NULL αυτής της στήλης και θα μας δώσει το αποτέλεσμα που ψάχνουμε :

mysql> SELECT Authors.Name, COUNT(Jokes.ID) AS NumJokes
-> FROM Authors LEFT JOIN Jokes
-> ON AID = Authors.ID
-> GROUP BY AID;
+---------------+----------+
| Name          | NumJokes |
+---------------+----------+
| Amy Mathieson |        0 |
| Kevin Yank    |        3 |
| Joan Smith    |        1 |
| Ted E. Bear   |        5 |
+---------------+----------+

Η Έκφραση HAVING

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

mysql> SELECT Authors.Name, COUNT(Jokes.ID) AS NumJokes
-> FROM Authors LEFT JOIN Jokes
-> ON AID = Authors.ID
-> WHERE NumJokes = 0
-> GROUP BY AID;
ERROR 1054: Unknown column 'NumJokes' in 'where clause'

Ο λόγος που η έκφραση WHERE NumJokes = 0 δεν μας έκανε τη δουλειά είναι επειδή οι συνθήκες στην έκφραση WHERE επηρεάζουν τις καταχωρήσεις που έχουν επιλεγεί πριν ομαδοποιηθούν σύμφωνα με την έκφραση GROUP BY.
Έτσι, αν θελήσουμε να αποκλείσουμε από το μέτρημα τα jokes που περιέχουν τη λέξη “chicken”, θα μπορούσαμε να χρησιμοποιήσουμε την έκφραση WHERE, όμως εφόσον η στήλη NumJokes δεν υπάρχει πριν επενεργήσει η GROUP BY, θα πρέπει να χρησιμοποιήσουμε μια διαφορετική μέθοδο για να ορίσουμε συνθήκες.
Οι συνθήκες που επηρεάζουν τα αποτελέσματα αφού έχει γίνει η ομαδοποίηση (grouping) πρέπει να εμφανισθούν σε μια ειδική έκφραση HAVING. Έτσι λοιπόν, το σωστό ερώτημα είναι το εξής :

mysql> SELECT Authors.Name, COUNT(Jokes.ID) AS NumJokes
-> FROM Authors LEFT JOIN Jokes
-> ON AID = Authors.ID
-> GROUP BY AID
-> HAVING NumJokes = 0;
+---------------+----------+
| Name          | NumJokes |
+---------------+----------+
| Amy Mathieson |        0 |
+---------------+----------+

Μερικές συνθήκες εργάζονται και στις δύο εκφράσεις HAVING και WHERE. Για παράδειγμα, αν θελήσουμε να αποκλείσουμε έναν συγκεκριμένο συγγραφέα με το όνομά του, θα μπορούσαμε να χρησιμοποιήσουμε το Authors.Name != “AuthorName” σε μια από τις εκφράσεις WHERE ή HAVING, επειδή αν φιλτράρουμε τον συγγραφέα πριν ή μετά από την ομαδοποίηση, θα πάρουμε τα ίδια αποτελέσματα.
Σε μερικές περιπτώσεις, είναι πάντα καλύτερο να χρησιμοποιούμε την έκφραση WHERE, επειδή η MySQL είναι καλύτερη στο να βελτιστοποιεί εσωτερικά τέτοια ερωτήματα και έτσι εκτελούνται ταχύτερα.