Im rewriting some queries today. Im wondering which is faster here. My current query is as follows:
WITH
Q1 AS (
SELECT ID, A1, A2, A3
FROM TBL1
WHERE
A1 = 'SOMETHING'
),
Q2 AS (
SELECT ID, B1, B2, B3
FROM TBL2
INNER JOIN TBL1 ON TBL2.ID = TBL1.ID
)
Q3 AS (
SELECT ID, C1, C2, C3
FROM TBL3
INNER JOIN TBL1 ON TBL3.ID = TBL1.ID
)
SELECT ID, A1, A2, A3, B1, B2, B3, C1, C2, C3
FROM
TBL1
LEFT JOIN TBL2 ON TBL1
LEFT JOIN TBL3 ON TBL1
… and so forth through quite a bit of tables. Is it better to run a query like this or to just do the query the normal way? My thinking here is that tbl1 has millions of records and only about 500k records apply to me (figured out by
A1 = ‘SOMETHING’) and then inner joining a1 against the other tables (which also have millions of records) would limit results down on those other tables.