פישוט שאילתות באמצעות מילת המפתח WITH

חלק מהיחידה יסודות במסלול ה-SQL של Coddy. שיעור 49 מתוך 72.

שאילתות עלולות להפוך למסורבלות מדי כשמוסיפים שאילתות פנימיות רבות. לדוגמה, הנה שאילתה שיש בה שאילתות משנה רבות:

SELECT * FROM table1
WHERE col2 IN (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN (
		SELECT col5 FROM table3
		WHERE col1 AND col3 OR col2
	)
)

כדי להקל עלינו, נוכל להשתמש במילת המפתח WITH query_name AS (...). היא מאפשרת לנו לשמור שאילתה בשם ולהשתמש בה בכל מקום שנרצה:

WITH query1 AS (
	SELECT col5 FROM table3
	WHERE col1 AND col3 OR col2
), query2 AS (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN (SELECT col5 FROM query1)
)
SELECT * FROM table1
WHERE col2 IN (SELECT col1 FROM query2) AND col4 IN (SELECT col5 FROM query1)

כאן השתמשנו שוב ב־query1 בתוך query2, ושוב בשאילתה הראשית כדי להוסיף תנאי נוסף על col4, שאין בגרסה המקוננת שמופיעה למעלה. שאילתה בעלת שם שנוצרת באמצעות WITH נקראת CTE (ביטוי טבלה נפוץ).

אתגר

קל

טבלאות ועמודות זמינות:
devices_specs: device_id, width, height, num_features, opinion
devices_score: device_id, score

כתבו שאילתה ש:

תחילה מזהה "large devices" (מכשירים שרוחבם width גדול מ־200)
לאחר מכן מחשבת את הציון הממוצע עבור המכשירים הגדולים האלה. תנו לעמודה הזו את השם average_score

השתמשו בסעיף WITH כדי לפתור את הבעיה.

נסו בעצמכם

-- שלב 1: תנו שם לקבוצת תוצאות זמנית שמכילה רק את המכשירים שמעניינים אתכם
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- שלב 2: השתמשו שוב בקבוצת התוצאות הזמנית הזו בתוך השאילתה הראשית
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);