I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use correlated sub-queries to check the state of flags in other tables to determine Eligibility, or am I better of creating Views that utilize Joins that represent the valid Records? As a more concrete example, is this:
Select Col1, Col2, Col3 From Table1 Where (Select RefCol From Table2 Where Table2.PK = Table1.FK) = "Condition"
Superior or Inferior to something like this:
Select T1.Col1, T1.Col2, T1.Col3 From T1 Inner Join T2 On T1.FK = T2.PK Where T2.RefCol = "Condition"
-- Edited --
As a corollary question: Is it productive to create Views which contain Intermediary validations? IE, if I repeatedly need to check if
T1.Col1 = 1 and
T1.Col2 = 0 is it worthwhile to create the following view:
Create View T1Validated As Select Col1, Col2 From T1 Where Col1 = 1 And Col2 = 0
And then later use the existence of a record in T1Validated in subsequent checks, or is that likely to produce additional database page retrievals and/or table scans?