SQL arbeitet nicht mit temporären Tabellen für die Abfrage, so wie wir uns das immer vorstellen.
Meisten werden aus Performancegründen nur fehlende Indizes aufgebaut.
Das Resultset kann schon mal in einer temporären Tabelle landen, aber das ist auch nicht sehr häufig so.
Dies merkt man dann daran, dass beim Lesen eines Resultsets bei komplexen Gruppierungen immer wieder Pausen auftreten wo dann nichts passiert.
Beim STRSQL z.B. kommen viele Ergebnisse erst sehr schnell. Es macht sich aber kaum jemand die Mühe, das Ergebnis mal durchzublättern.
Da steht der Job und in der Statuszeile werden plötzlich Milliarden Zugriffe gezählt, obwohl doch gar nicht soviele Quelldaten vorhanden sind. Dies liegt dann z.B. am fehlenden Index, die Tabelle ist u.U. zu klein als das es sich lohnt und so wird dann mal eben für jede Masterzeile ein Tablescan pro Jointable durchgeführt.
Wer z.B. glaubt, dass eine "Common Table Expression" (CTE) eine tatsächliche Zwischentabelle darstellt der irrt sich. Diese dient ausshließlich dafür uns das Tippen zu erleichtern.
Alternativ könntest du auch je CTE entsprechend oft eine "Derived Table" einbauen, also "select * from (select * from B) A", was in der Performance auf dasselbe rausläuft.
Ich habe schon viele SQL's analysiert und umgebaut bis hin zu wirklichen temporären Tabellen in der QTEMP. Also CTE-Ausdrücke vorab in die QTEMP gestellt, einen Index dazu und dieses Ergebnis (oder auch mehrere) dann im endgültigen Query zusammengefasst.
Somit konnte ich Laufzeiten von Minuten auf Sekunden reduzieren.
Eine große Hilfe ist meist einfach der DEBUG-Modus mit den Hinweisen und Vorschlägen.
Auch die Zugriffspfadanalyse eines SQL's ist da schon aussagefähig. Immerhin wird da nie darauf hingewiesen, dass tatsächlich eine Tabelle erstellt wird sondern immer nur, welche Indizes verwendet, wann ein Tablescan durchgeführt wird und welche Pfade empfehlenwert wären.
Dies kann man dann auch z.B. per DSPJOB, Auswahl 14, sehen wie da die Dateizugriffe hochgezhählt werden. Teilweise erfolgen da für einen "Select into ", also 1 Ergebniszeile, durchaus mehrere 1000 Zugriffe!
Diese Empfehlungen sind i.Ü. nie statisch sondern können sich je nach Datenvolumen plötzlich ändern.
Bis 100.000 Zeilen gings noch gut, bei 500.000 Zeilen schon weniger und bei 1.000.000 wurde plötzlich gestreikt. Wohl gemerkt, immer beim selben SQL, allerdings über die Jahre.
Was Birgitta da mit "die Datenbank ist ja bereits im System enthalten" für einen Vorteil sieht (außer halt die Lizenzkosten) weiß ich nicht, aber das ist kein echter Vorteil.
Es kommt immer darauf an, wie gut ist SQL implementiert.
Die AS/400 ist da gewachsen und hat auch wirklich eine sehr gute Datenbank. Allerdings basiert sie immer noch auf dem guten alten PF/LF-System, denn es sind immer noch native-Zugriffe ohne SQL möglich! M.a.W.: SQL ist eine Zwischenschicht zwischen RPG/COBOL/usw. und eben den originären QDBPUT und QDBGET-Befehlen. Deshalb ist ein RLA (Record Level Access) immer noch in vielen Punkten messbar schneller. Während SQL noch bei der Optimierung ist, ist das RPG schon fertig.
Vergleiche da einen simplen CHAIN mit einem "Select * into : HostDS from Myfile where K1='A' and K2=5 and ...".

Andere DB-Systeme haben diesen Overhead nicht und sind deshalb auch nicht unbedingt schlechter.
Wobei zugegebn, der Microsoft-SQL-Server auch nicht meine erste Wahl ist.

Und was soll der Hinweis auf Vector-Indizes (EVI's)?
Mir ist doch egal, welche Art der Indizierung eine DB macht, Hauptsache sie macht es gut.
Die DB2 verwendet auch schon mal durchaus mehrere Indizes, erstellt eine "RRN"-Bit-Map per XOR und das Ergebnis sind die RRN's, die zu verarbeiten sind.

Was das Umschreiben der SQL's angeht, so bin ich da auch schn bei Release-Wechseln reingefallen.
Da gab es bis V5R4 den "Select * from TEIL where TEFIRM = '1' and TEWKNR = 000 and ..."
Nun, wer Infor-XPPS kennt sieht, dass die Hochkommata fur das Werk 000 fehlen, das hat allerdings keiner gemerkt, da der Optimizer das auf Grund des Feldes TEWKNR erkannt hat, die Hochkomma selber dazufügt und den Index verwendete.
Dann kam V6R1 und der Optimzer änderte seine Priorität.
Sämtliche Konstanten werden zuerst analysiert, in interne Hostvariablen kopiert und Parametermarker gesetzt. Auf Grund der Typisierung DEC(3, 0) wurde die Abfrage geändert in "cast(TEWKNR as dec(3, 0)) = ?".
Das Ergebnis war nun:
1. Keine Verwendung eines Indizes
2. Durchführung eines Tablescans
3. Irgend ein Idiot hatte mal in das Feld TEWKNR einen nicht numerischen Wert reingestellt und der SQL starb immer genau an dieser Stelle.
Leider hat dies ein paar Tage niemand bemerkt, da ja der Programmierer davon ausging, dass dieser SQL auf Grund der Datenlage nie auf einen Fehler laufen konnte und deshalb keinen SQLCODE abfragte.
Soviel zum Optimizer und dem Umschreiben.

Und zu guter Letzt:
Leider ist das meiste der SQL-Interna nicht dokumentiert. Man kann sich mal die Mühe eines Jobtraces (Call-Aufrufe) machen bei einem SQL-Programm oder einem RLA-Programm. Man kommt da aus dem Staunen nicht heraus.
Schade, ich hatte mal bei V5 oder V6 einen SQL-Fehler gemeldet. Hierzu bekam ich irgend eine Anweisung für erweiterte Ausgaben des SQL-Optimizers, die ich mir leider nicht gemerkt habe!
Da bekam man einen schönen Spool mit über 100 Seiten, der den gesamten Analysevorgang und auch die Umbauten protokollierte. Da kam man aus dem Staunen wirklich nicht mehr heraus.

Und was deine IN-Liste angeht, ja so würde ich das machen, SQL spart sich aber wirklich das Aufbauen von temporären Tabellen.

Fazit:
Ich finde SQL wirklich gut und es erleichtert ungemein die Arbeit.
Aber man muss sich genau überlegen was und wie man es macht, denn SQL ist auch nur von Menschen programmiert.