Method and system for generating SQL joins to optimize performance

A method for creating a query for a database, wherein the method automatically prevents inclusion of an unnecessary table join in the query, the method comprising:

• determining if a plurality of parameters are on an added aliases list;
• responsive to the determination that the parameters are not on the added aliases list, running a clause generation program;
• determining if all of the parameters have been analyzed;
• responsive to the determination that all of the parameters have been analyzed, determining whether an optional where clause alias is on the added aliases list;
• responsive to the determination that the optional where clause alias is on the added aliases list, generating a (join) WHERE clause for the optional where clause alias;
• and
• responsive to the determination that the optional where clause alias is on the added aliases list, adding the (join) WHERE clause to a (join) WHERE clause string.

A program product operable on a computer, the program product comprising:

• a computer-usable medium;
• wherein the computer usable medium comprises instructions comprising:
• instructions for determining whether a SQL template has a FROM clause placeholder and determining whether a FROM clause table has been previously specified in the SQL template;
• responsive to the determination that the SQL template has the FROM clause placeholder and that the FROM clause table has not been previously specified in the SQL template, instructions for generating a FROM clause for the table;
• instructions for determining whether the SQL template has a JOIN clause placeholder and determining whether the FROM clause has been added;
• and
• responsive to the determination that the SQL template has the JOIN clause placeholder and that the FROM clause has not been added, instructions for generating a JOIN clause.

A program product operable on a computer, the program product comprising:

• a computer-usable medium;
• wherein the computer usable medium comprises instructions comprising:
• instructions for determining if a plurality of parameters are on an added aliases list;
• responsive to the determination that the parameters are not on the added aliases list, instructions for running a clause generation program;
• determining if all of the parameters have been analyzed;
• responsive to the determination that all of the parameters have been analyzed, instructions for determining whether an optional where clause alias is on the added aliases list;
• responsive to the determination that the optional where clause alias is on the added aliases list, instructions for generating a (join) WHERE clause for the optional where clause alias;
• and responsive to the determination that the optional where clause alias is on the added aliases list, instructions for adding the (join) WHERE clause to a (join) WHERE clause string.

A program product operable on a computer, the program product comprising:

• a computer-usable medium;
• wherein the computer usable medium comprises instructions comprising:
• a query program, wherein the query program queries a database using a query;
• a query generation program, wherein the query generation program generates a query having only the necessary joins for the query;
• and
• a clause generation program, wherein the clause generation program determines which clauses are necessary in the query.