What's the best way to do multiple join in this SQL query?
I am trying to fetch data from a table (a.table) to join another table (b.table). To do this, I need to join a third table (c.table) for the link between the Plan_Code and Policy_Riders tables. See code below
USE [CDS]
GO
SELECT riders.ExpiryDt--
,riders.TerminationDt
,[ModalPremium]--
,plan_code
FROM a.table as riders
JOIN c.table as policy
ON policy.Policy_Num = riders.Policy_Num
JOIN b.table AS plan_code
on policy.Plan_Code_ID = plan_code.Plan_Code_ID
WHERE plan_code.Plan_Code LIKE '%EIUL3%'
OR plan_code.Plan_Code LIKE '%LBIUL%'
OR plan_code.Plan_Code LIKE '%MEIUL3%'
GO
To get the Plan_code field name from b.table to my output, I need to join the first table a.table to c.table and then c.table to b.table. My question is, is there a better way to approximate this query to better join between the three tables? Any help would be greatly appreciated. Thank!
source to share
First of all, use a derived table for filters:
...
JOIN (SELECT columns
FROM b.table
WHERE Plan_Code LIKE '%EIUL3%'
OR Plan_Code LIKE '%LBIUL%'
OR Plan_Code LIKE '%MEIUL3%'
) AS plan_code ON policy.Plan_Code_ID = plan_code.Plan_Code_ID
Typically, these filters are applied to a minimal set of data, rather than after all tables have been joined. Another option is to make the above temporary table and then join to it. The same concept, just helping the optimizer run efficiently. In smaller queries, you won't see the difference, but in larger queries (especially ones like this, with many filters from the same table) it will be night and day.
Second, your filters are specific. Usage LIKE
with forward and backward wildcards is %ex%
not very good. It won't be able to use indexes. Use one ex%
or the other %ex
if possible.
Other than that, your joins are fine and are the right approach for getting columns from each table.
source to share