Subquery (תת שאילתה) הוא בדיוק מה שהשם מרמז: פקודת SELECT שמוחבאת בתוך פקודה אחרת, עטופה בסוגריים. SQLite מריץ את השאילתה הפנימית, לוקח את התוצאה שלה ומעביר אותה לחיצונית.
נכין דוגמה קטנה שנשתמש בה שוב ושוב:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
SELECT * FROM orders;
חמש הזמנות, ארבעה לקוחות, ושניים מהם לא הזמינו כלום. נשתמש בזה לאורך כל הדף.
Subquery ב-WHERE: סינון לפי רשימה
הצורה הנפוצה ביותר: שולפים רשימת מזהים בשאילתה פנימית, ואז מסננים את השאילתה החיצונית לפיה.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);
השאילתה הפנימית מפיקה כל customer_id שמופיע ב-orders. השאילתה החיצונית משאירה רק את הלקוחות שה-id שלהם נמצא ברשימה הזו. Cleo, Boris ו-Ada מופיעים. Dmitri (בלי הזמנות) לא.
IN (SELECT ...) הוא התבנית המרכזית ל"שורות ב-A שיש להן התאמה ב-B". בראש, קראו אותו כ"כאשר הערך של העמודה הזו הוא אחד מהערכים שהשאילתה הפנימית מחזירה".
NOT IN: שימו לב ל-NULL
השאלה ההפוכה, "אילו לקוחות לא הזמינו?", נמצאת במרחק שורה אחת:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
כאן זה עובד. אבל ל-NOT IN יש קצה חד: אם תת השאילתה מחזירה אי פעם NULL, כל ה-NOT IN הופך ל-NULL (שהוא לא TRUE), ומקבלים אפס שורות. מפתיע ושקט.
ההרגל הבטוח כשמשתמשים ב-NOT IN מול עמודה שעשויה להכיל NULL:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
SELECT name
FROM customers
WHERE id NOT IN (
SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);
או השתמשו ב-NOT EXISTS, שאין לו את הבעיה הזו בכלל. נגיע לזה.
Subquery סקלרי: שורה אחת, עמודה אחת
תת שאילתה סקלרית מחזירה ערך בודד, שורה אחת ועמודה אחת, ואפשר להשתמש בה בכל מקום שבו מצופה ערך.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
SELECT name, total
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE total = (SELECT MAX(total) FROM orders);
ה-SELECT MAX(total) FROM orders הפנימי מחזיר 200. השאילתה החיצונית מסננת אז את ההזמנות שתואמות לערך הזה. שימושי בכל פעם שצריך להשוות מול ערך מצטבר.
אפשר גם להשתמש בתת שאילתה סקלרית ברשימת ה-SELECT כדי לצרף ערך מחושב לכל שורה:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
SELECT
name,
(SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count
FROM customers;
כל שורה של customers מריצה את השאילתה הפנימית פעם אחת, עם customers.id במקום המתאים. זה subquery מתואם, עוד על זה בהמשך. במקרים של "מספר אחד לכל שורה" כמו זה, LEFT JOIN עם GROUP BY בדרך כלל מהיר יותר, אבל הצורה הסקלרית קריאה להפליא.
EXISTS: רק לבדוק אם משהו תואם
EXISTS הוא בן הדוד השקט של IN. הערכים לא מעניינים אותו: הוא רק בודק אם תת השאילתה מחזירה שורה כלשהי. בדרך כלל כותבים בפנים SELECT 1, כי העמודה לא משנה.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
SELECT name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.total > 100
);
זה מוצא לקוחות שביצעו לפחות הזמנה אחת מעל 100. השאילתה הפנימית מתייחסת ל-c.id מהשאילתה החיצונית, וזה מה שהופך אותה למתואמת. SQLite מפסיק לסרוק את הטבלה הפנימית ברגע שהוא מוצא התאמה, ולכן EXISTS לעיתים קרובות מהיר יותר מ-IN בשאלות כמו "האם לשורה הזו יש שורה קשורה?".
השלילה, NOT EXISTS, היא הדרך הבטוחה מבחינת NULL לשאול "אין שורה קשורה":
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
SELECT name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
Subquery ב-FROM: טבלה נגזרת
תת שאילתה יכולה לעמוד בכל מקום שבו יכולה לעמוד טבלה, כולל פסוקית FROM. השאילתה הפנימית הופכת ל"טבלה נגזרת" זמנית עם שם, שאפשר לעשות עליה join, סינון או צבירה.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
SELECT country, AVG(customer_total) AS avg_per_customer
FROM (
SELECT c.country, c.id, SUM(o.total) AS customer_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
) AS per_customer
GROUP BY country;
השאילתה הפנימית מחשבת סכום לכל לקוח. השאילתה החיצונית מחשבת את הממוצע של הסכומים האלה לכל מדינה. צבירות דו שלביות כאלה הן בדיוק המטרה של טבלאות נגזרות: כשאי אפשר לעשות הכול ב-GROUP BY אחד.
הכינוי AS per_customer הוא חובה: לכל טבלה נגזרת צריך להיות שם.
Subquery מתואם: ריצה לכל שורה חיצונית
תת שאילתה היא מתואמת כשהיא מתייחסת לעמודה מהשאילתה החיצונית. SQLite צריך לחשב מחדש את השאילתה הפנימית עבור כל שורה חיצונית, וזה גמיש אבל יכול להיות יקר.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
SELECT name,
(SELECT MAX(total)
FROM orders
WHERE customer_id = customers.id) AS biggest_order
FROM customers;
לכל לקוח, מצאו את ההזמנה הגדולה ביותר שלו. השאילתה הפנימית תלויה ב-customers.id, ולכן היא רצה פעם אחת לכל לקוח. לקוחות בלי הזמנות מקבלים NULL, וזה בדיוק מה שהייתם רוצים.
Subquery מתואם מתאים באופן טבעי ל"עבור כל שורה ב-A, חשב משהו מ-B". אם הטבלה קטנה או שהחיפוש משתמש באינדקס, זה בסדר. בטבלאות גדולות בלי אינדקסים מתאימים, מדדו ביצועים לפני שאתם משחררים: JOIN עם GROUP BY מהיר יותר לעיתים קרובות.
Subquery מול JOIN: במה לבחור?
שתי השאילתות האלה עונות על אותה שאלה:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
-- Subquery
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);
-- JOIN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;
שתיהן מחזירות את אותן שורות. האופטימייזר של SQLite כותב לעיתים קרובות צורה אחת כצורה השנייה באופן פנימי. בחרו לפי קריאות:
השתמשו ב-subquery כשצריך רק לסנן, ואתם לא רוצים שעמודות מהטבלה הפנימית ילכלכו את התוצאה.
השתמשו ב-JOIN כשהתוצאה צריכה עמודות משתי הטבלאות.
השתמשו ב-EXISTS כשאתם שואלים "האם קיימת לפחות שורה קשורה אחת?": זה ברור יותר ונמנע ממלכודות ה-NULL של IN/NOT IN.
כשיש ספק, כתבו את הגרסה שמסבירה את עצמה כשקוראים אותה בקול.
מלכודת נפוצה: subquery שמחזיר כמה שורות
תת שאילתה שמשמשת עם = חייבת להחזיר לכל היותר שורה אחת. אם היא מחזירה יותר, SQLite בוחר אחת (למעשה באקראי) ומקבלים תוצאות שגויות בשקט, בלי שגיאה.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
-- Risky: what if more than one customer is from the UK?
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'UK');
השתמשו ב-IN כשהשאילתה הפנימית עשויה להחזיר כמה שורות:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
country TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total REAL
);
INSERT INTO customers (id, name, country) VALUES
(1, 'Ada', 'UK'),
(2, 'Boris', 'FR'),
(3, 'Cleo', 'UK'),
(4, 'Dmitri','DE');
INSERT INTO orders (customer_id, total) VALUES
(1, 120), (1, 80), (2, 50), (3, 200), (3, 30);
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'UK');
אם אתם מצפים לשורה אחת בדיוק ורוצים לאכוף את זה, הוסיפו LIMIT 1 ו-ORDER BY כדי שהבחירה תהיה לפחות דטרמיניסטית. עדיף: כתבו את השאילתה כך שהנתונים עצמם יבטיחו שורה אחת (סננו לפי עמודה ייחודית).
הבא בתור: Common Table Expressions
תת שאילתות ב-FROM הופכות למסורבלות מהר, במיוחד כשצריך את אותה טבלה נגזרת פעמיים, או כשהקינון מגיע לשלוש רמות. Common Table Expressions (WITH ... AS (...)) מאפשרות לתת שם לתת שאילתה מראש ולהתייחס אליה בשמה בהמשך הפקודה. זה הדף הבא.
שאלות נפוצות
מה זה subquery ב-SQLite?
Subquery (תת שאילתה) היא פקודת SELECT שמקוננת בתוך פקודה אחרת, עטופה בסוגריים. SQLite מריץ את השאילתה הפנימית ומעביר את התוצאה שלה לחיצונית. תת שאילתות יכולות להופיע ב-WHERE, ב-FROM, ב-SELECT ובעוד כמה פסוקיות.
מה ההבדל בין IN ל-EXISTS ב-SQLite?
IN (SELECT ...) בודק אם ערך תואם לשורה כלשהי שתת השאילתה מחזירה. EXISTS (SELECT ...) רק בודק אם תת השאילתה מפיקה שורה כלשהי בכלל, והערכים לא מעניינים אותו. EXISTS הוא בדרך כלל הבחירה הטובה יותר כשהשאילתה הפנימית מתייחסת לשורה החיצונית (subquery מתואם).
האם להשתמש ב-subquery או ב-JOIN ב-SQLite?
השתמשו ב-JOIN כשצריך בתוצאה עמודות משתי הטבלאות. השתמשו בתת שאילתה כשצריך רק לסנן או לחשב ערך בודד. האופטימייזר של SQLite ממילא כותב לעיתים קרובות צורה אחת כצורה השנייה, אז בחרו במה שקריא יותר.
מה זה subquery מתואם ב-SQLite?
Subquery מתואם מתייחס לעמודה מהשאילתה החיצונית, ולכן צריך לחשב אותו מחדש עבור כל שורה חיצונית. הוא גמיש אבל עלול להיות איטי בטבלאות גדולות. אם subquery מתואם מתגלה כצוואר בקבוק, כתיבה מחדש שלו כ-JOIN או כ-CTE עוזרת לעיתים קרובות.
