List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:August 22 2012 9:38pm
Subject:Re: help with correlated subquery
View as plain text  
Hello Martin,

On 8/22/2012 8:30 AM, Martin Gainty wrote:
>
> assign realistic alias names
> OuterJoin should be called OuterJoin
> InnerJoin should be called InnerJoin
>

Almost!  MySQL does not have a simple OUTER JOIN command (some RDBMSes 
call this a FULL OUTER JOIN). What we do have is the option to include 
the OUTER keyword into our LEFT or RIGHT joins. For example, both of 
these are acceptable:

LEFT OUTER JOIN
LEFT JOIN

Also, you need a space between "inner" and "join" as in INNER JOIN.

> If you want only the most restricitive criteria that match resultsets from both
> select statements use INNER JOIN
> if you want all results from both resultsets (cartesian JOIN) use OUTER JOIN
>

Again, MySQL does not have a plain OUTER JOIN. If you want a full 
Cartesian product of two tables, use the 'comma join' syntax with no 
criteria for matching the tables in the WHERE clause.

SELECT ... FROM tableA, tableB WHERE ....

Syntax details are located here:
http://dev.mysql.com/doc/refman/5.5/en/join.html


> Finally: Be aware FUNCTIONS such as AVG cast off indexing and should be avoided
> unless the FUNCTION(columnName) itself is indexed
> GROUP BY re-arranges your query so its best to introduce GROUP BY in stages
>

Not exactly. If you wrap a column in a function and attempt to use the 
results of that function in the WHERE clause, then you are correct. 
However based on the way your define your indexes, the data you process 
in a function may actually come from the index and save you a trip to 
the underlying table. In this case, the index could make your function 
faster by skipping an additional retrieval step.


> use realistic alias names like Dept and EmployeeNumber and avoid aliases that cause
> confusion like 'a' or 'foo'

Excellent advice.

> Develop in stages and write down what YOU EXPECT vs WHAT each query produces ..
> If the Individual Select doesnt produce expected results..STOP and correct the SELECT
> Statement

Also excellent advice.

> Diagram out what you expect results from any of the INNER JOIN, OUTER JOIN, LEFT JOIN
> or RIGHT JOIN operations will produce
> If the executed JOIN Statement does not produce expected results STOP and correct the
> JOIN clause BEFORE incorporating more functionality
>
> Obfuscation and confusion can hopelessly sidetrack any intelligent analysis

Well put!

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN


Thread
help with correlated subqueryLarry Martell21 Aug
  • RE: help with correlated subqueryRick James21 Aug
    • Re: help with correlated subqueryLarry Martell22 Aug
  • Re: help with correlated subqueryhsv22 Aug
    • Re: help with correlated subqueryLarry Martell22 Aug
      • RE: help with correlated subqueryRick James22 Aug
      • Re: help with correlated subqueryLarry Martell23 Aug
        • Re: help with correlated subqueryLarry Martell23 Aug
Re: help with correlated subqueryLarry Martell22 Aug
RE: help with correlated subqueryMartin Gainty22 Aug
  • Re: help with correlated subqueryShawn Green22 Aug
    • Re: JOIN, JOIN, JOINhsv23 Aug