Η COALESCE επιστρέφει την πρώτη τιμή της λίστας της που δεν είναι NULL. Έτσι η SQL αντικαθιστά μια τιμή που λείπει με μια εναλλακτική. Τα παραδείγματα λειτουργούν σε PostgreSQL, MySQL, SQLite και SQL Server.
Πώς να χρησιμοποιήσεις τη COALESCE
Δώσε στη COALESCE όσες τιμές θέλεις. Τις ελέγχει από αριστερά προς τα δεξιά και επιστρέφει την πρώτη που δεν είναι NULL, ή NULL αν είναι όλες NULL:
COALESCE(value1, value2, ...)
SELECT COALESCE(NULL, 'second', 'third');
-- Output: second
SELECT COALESCE(NULL, NULL);
-- Output: NULL
Σε ένα πραγματικό query οι τιμές είναι συνήθως στήλες, με μια σταθερή τιμή στο τέλος ως έσχατη λύση:
-- Input: users
-- id | name | nickname
-- ---+--------------+---------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | NULL
-- 3 | Priya Shah | P
SELECT id, name, COALESCE(nickname, name) AS display_name
FROM users;
-- Output:
-- id | name | display_name
-- ---+--------------+-------------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | Tom Becker
-- 3 | Priya Shah | P
Κανόνες που πρέπει να ξέρεις
- Σταματά στην πρώτη τιμή που δεν είναι NULL, οπότε τα επόμενα ορίσματα δεν αξιολογούνται μόλις βρεθεί μία. Βάλε πρώτη την τιμή που είναι πιο συχνά συμπληρωμένη.
- Κάθε όρισμα χρειάζεται συμβατό τύπο. Ο συνδυασμός αριθμού με κείμενο αποτυγχάνει σε PostgreSQL και SQL Server, οπότε κάνε πρώτα μετατροπή τύπου:
-- Input: products
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | NULL
SELECT name, COALESCE(stock, 'N/A') AS stock FROM products;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "N/A"
-- SQL Server: Conversion failed when converting the varchar value 'N/A' to data type int.
-- (in MySQL, cast with CAST(stock AS CHAR))
SELECT name, COALESCE(CAST(stock AS VARCHAR(10)), 'N/A') AS stock FROM products;
-- Output:
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | N/A
-
Ένα κενό string δεν είναι NULL. Η COALESCE αφήνει το
''να περάσει κατευθείαν. Τύλιξε τη στήλη σεNULLIF(column, '')για να θεωρούνται κι αυτά ως τιμές που λείπουν:
SELECT COALESCE('', 'No name');
-- Output: '' (an empty string)
SELECT COALESCE(NULLIF('', ''), 'No name');
-- Output: No name
Εναλλακτική σύνταξη
Θα δεις επίσης συντομεύσεις δύο ορισμάτων: IFNULL σε MySQL και SQLite, και ISNULL στον SQL Server:
SELECT IFNULL(NULL, 'fallback');
-- Output: fallback
Προτίμησε τη COALESCE. Δέχεται όσα ορίσματα θέλεις, ενώ η ISNULL του SQL Server δίνει στο αποτέλεσμα τον τύπο του πρώτου ορίσματος, κάτι που μπορεί να κόψει την εναλλακτική σου τιμή:
-- SQL Server
DECLARE @code VARCHAR(2) = NULL;
SELECT ISNULL(@code, 'none') AS with_isnull, COALESCE(@code, 'none') AS with_coalesce;
-- Output:
-- with_isnull | with_coalesce
-- ------------+--------------
-- no | none
Μια έκφραση CASE μπορεί να κάνει την ίδια δουλειά, πιο αναλυτικά: το COALESCE(nickname, name) είναι συντομογραφία του CASE WHEN nickname IS NOT NULL THEN nickname ELSE name END. Δες τον οδηγό για το SQL CASE για όταν χρειάζεσαι περισσότερες δυνατότητες.
Πότε να χρησιμοποιείς τη COALESCE;
Χρησιμοποίησε τη COALESCE όποτε ένα NULL θα έκανε το αποτέλεσμά σου λάθος, άσχημο ή κενό. Οι περισσότερες χρήσεις ανήκουν σε μία από αυτές τις τέσσερις.
Εναλλακτική από άλλη στήλη
Όταν η τιμή που θέλεις βρίσκεται σε μία από πολλές στήλες, η COALESCE διαλέγει την πρώτη που είναι συμπληρωμένη: ένα ψευδώνυμο ή ένα πλήρες όνομα, ένα κινητό ή ένα σταθερό, μια διεύθυνση αποστολής ή χρέωσης.
Προεπιλεγμένη τιμή όταν λείπουν όλες
Βάλε μια σταθερή τιμή στο τέλος της λίστας και το αποτέλεσμα δεν θα είναι ποτέ NULL. Αυτό θέλεις σε αναφορές και οθόνες, όπου το «Άγνωστη διεύθυνση» ή το 0.00 διαβάζεται καλύτερα από ένα κενό.
Συμπλήρωσε κενά με υπολογισμένη τιμή
Η εναλλακτική δεν χρειάζεται να είναι στήλη ή σταθερά. Μπορεί να είναι μια έκφραση, όπως ένα όνομα χρήστη ή ένα email που φτιάχνεται από άλλες στήλες, κάτι που κάνει τη COALESCE χρήσιμη για τον καθαρισμό εισαγόμενων δεδομένων.
Κράτα τα NULL έξω από τους υπολογισμούς
Κάθε αριθμητική πράξη με NULL δίνει NULL, οπότε μια έκπτωση ή ένα μπόνους που λείπει μηδενίζει όλο το σύνολο. Το COALESCE(discount, 0) μετατρέπει την τιμή που λείπει σε αριθμό που μπορεί να χρησιμοποιήσει ο υπολογισμός.
Πραγματικά παραδείγματα COALESCE
Εμφάνισε μια τιμή, όποια υπάρχει
Τα προϊόντα μπορεί να έχουν τιμή προσφοράς, κανονική τιμή ή (πριν την κυκλοφορία) καμία από τις δύο. Έλεγξέ τες με τη σειρά και βάλε προεπιλογή το μηδέν:
-- Input: products
-- product_id | name | sale_price | regular_price
-- -----------+---------------+------------+--------------
-- 1 | Desk Lamp | 19.99 | 24.99
-- 2 | Office Chair | NULL | 149.00
-- 3 | Standing Desk | NULL | NULL
SELECT product_id, name, COALESCE(sale_price, regular_price, 0.00) AS price
FROM products;
-- Output:
-- product_id | name | price
-- -----------+---------------+-------
-- 1 | Desk Lamp | 19.99
-- 2 | Office Chair | 149.00
-- 3 | Standing Desk | 0.00
Διάλεξε το καλύτερο τηλέφωνο για κλήση
Οι επαφές έχουν έως τρία τηλέφωνα. Εμφάνισε το πρώτο διαθέσιμο και ενημέρωσε όταν δεν υπάρχει κανένα:
-- Input: contacts
-- name | primary_phone | mobile_phone | work_phone
-- -----+---------------+--------------+-----------
-- Ada | 555-0101 | 555-0199 | NULL
-- Bo | NULL | 555-0142 | 555-0177
-- Cy | NULL | NULL | 555-0123
-- Di | NULL | NULL | NULL
SELECT name, COALESCE(primary_phone, mobile_phone, work_phone, 'No phone') AS phone
FROM contacts;
-- Output:
-- name | phone
-- -----+---------
-- Ada | 555-0101
-- Bo | 555-0142
-- Cy | 555-0123
-- Di | No phone
Δημιούργησε ένα προσωρινό email
Οι υπάλληλοι χωρίς καταχωρημένο email παίρνουν ένα που φτιάχνεται από το όνομά τους, ώστε κάθε γραμμή του καταλόγου να έχει τιμή:
-- Input: employees
-- employee_id | first_name | last_name | email
-- ------------+------------+-----------+-----------------
-- 1 | Grace | Hopper | ghopper@navy.mil
-- 2 | Alan | Turing | NULL
SELECT
employee_id,
first_name || ' ' || last_name AS full_name,
COALESCE(
email,
LOWER(first_name) || '.' || LOWER(last_name) || '@company.com'
) AS email
FROM employees;
-- Output:
-- employee_id | full_name | email
-- ------------+--------------+------------------------
-- 1 | Grace Hopper | ghopper@navy.mil
-- 2 | Alan Turing | alan.turing@company.com
Σε MySQL και SQL Server, ένωσε τα strings με CONCAT() αντί για ||.
Σύνολο παραγγελίας με προαιρετικές εκπτώσεις
Χωρίς τη COALESCE, μία γραμμή χωρίς έκπτωση κάνει το σύνολο της παραγγελίας λάθος, επειδή η SUM παραλείπει εντελώς τις γραμμές με NULL:
-- Input: order_items
-- order_id | product | quantity | unit_price | discount
-- ---------+----------+----------+------------+---------
-- 101 | Keyboard | 1 | 49.00 | 5.00
-- 101 | Mouse | 2 | 19.50 | NULL
-- 102 | Monitor | 1 | 189.00 | NULL
-- 102 | Cable | 3 | 6.00 | 2.00
-- Wrong: lines with a NULL discount drop out of the total
SELECT order_id, SUM(quantity * unit_price - discount) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+------
-- 101 | 44.00
-- 102 | 16.00
-- Right: a missing discount counts as 0
SELECT order_id, SUM(quantity * unit_price - COALESCE(discount, 0)) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+-------
-- 101 | 83.00
-- 102 | 205.00
Περισσότερα για τη COALESCE
Σε αυτό το blog:
-
MySQL COALESCE: η COALESCE μαζί με την
IFNULLτης MySQL - Οδηγός για το SQL CASE: λογική συνθηκών πέρα από τους ελέγχους NULL
- Οδηγός για το SQL not equal: γιατί οι συγκρίσεις με NULL δεν ταιριάζουν ποτέ
- Οδηγός για το SQL GROUP BY: οι συναθροιστικές συναρτήσεις που συχνά τροφοδοτεί η COALESCE
- Οδηγός για το SQL SUBSTRING: εξαγωγή κειμένου από στήλες που μπορεί να είναι NULL
Επίσημη τεκμηρίωση:
Το Beekeeper Studio είναι ένα δωρεάν & open source GUI για βάσεις δεδομένων
Το καλύτερο εργαλείο για SQL queries & επεξεργασία που έχω χρησιμοποιήσει ποτέ. Μου δίνει ό,τι χρειάζομαι για να διαχειρίζομαι τη βάση δεδομένων μου. - ⭐⭐⭐⭐⭐ Mit
Το Beekeeper Studio είναι γρήγορο, εύχρηστο και κατανοητό με την πρώτη ματιά. Υποστηρίζει πάρα πολλές βάσεις δεδομένων και δουλεύει άψογα σε Windows, Mac και Linux.
Τι λένε οι χρήστες για το Beekeeper Studio
"Το Beekeeper Studio αντικατέστησε πλήρως την παλιά μου ροή εργασίας με SQL. Είναι γρήγορο, εύχρηστο και κάνει τη δουλειά με βάσεις δεδομένων ξανά απολαυστική."
"Έχω δοκιμάσει πολλά GUI για βάσεις δεδομένων, αλλά το Beekeeper πετυχαίνει την τέλεια ισορροπία ανάμεσα σε δυνατότητες και απλότητα. Απλώς δουλεύει."