Simplify Those Nested Joins |
|||
|
If you're new to SQL, then one of the first things you'll learn to do is join two tables
together, and produce output that contains fields from both tables. MS-Access users can easily join tables in the query designer too. And if you've ever peeked inside the SQL behind a more complicated query created using almost any query designer, you'll get an idea of how hideous SQL can get. An SQL query can be a lot easier to read, if you limit yourself to LEFT JOIN and INNER JOIN, and skip RIGHT JOIN completely. The MS-Access query designer produces SQL code that is full of all three, but one of them is redundant (it's only personal bias that makes me choose LEFT over RIGHT). SELECT * FROM t1 RIGHT JOIN t2 ON t1.IdField=t2.IdField is equivalent to SELECT * FROM t2 LEFT JOIN t1 ON t1.IdField=t2.IdField Now, you might think that SELECT * FROM (((((t1 LEFT JOIN t2 ON ConditionA) LEFT JOIN t3 ON ConditionB) LEFT JOIN t4 ON ConditionC) LEFT JOIN t5 ON ConditionD) LEFT JOIN t6 ON ConditionE) LEFT JOIN t7 ON ConditionF looks a little complex, but you'd have to admit that that's a lot easier to read than SELECT * FROM ((t5 RIGHT JOIN (t7 RIGHT JOIN (t3 RIGHT JOIN (t1 LEFT JOIN t2 ON ConditionA) ON ConditionB) LEFT JOIN t4 ON ConditionC) ON ConditionD) LEFT JOIN t6 ON ConditionE) ON ConditionF And I've seen much worse automatically generated SQL than that! The way I've neatly set out all my LEFT JOIN's, also makes it much easier to add a table to the query. And it's not difficult to dynamically generate SQL in this form at run-time. If you are writing an ad-hoc query generator, don’t be tempted to write SQL like this: SELECT * FROM (Big complicated join expression) WHERE (Data matches whatever the user asked for) Include only the tables that are needed in the query, and it will run faster. The inner-most table in the query (the first one in the list) should be the most important entity. Sample VB code follows: 'NumberOfIncludedTables includes MainTable. 'The following code assumes that at least one other table will 'be included in the query. GeneratedSql = "SELECT " & SelectedFields _ & " FROM " & String(NumberOfIncludedTables - 2, "(") & "MainTable" If Field1Selected Then GeneratedSql = GeneratedSql & " LEFT JOIN Table1 ON MainTable.Field1=Table1.KeyField)" End If If Field2Selected Then GeneratedSql = GeneratedSql & " LEFT JOIN Table2 ON MainTable.Field2=Table2.KeyField)" End If If Field3Selected Then GeneratedSql = GeneratedSql & " LEFT JOIN Table3 ON MainTable.Field3=Table3.KeyField)" End If 'The above code generates one more closing parenthesis 'than is needed, so strip off the last character. GeneratedSql = Left$(GeneratedSql, Len(GeneratedSql) - 1)
|