SQL: some basic JOIN syntax

INNER JOIN and , (comma) are equivalent in the absence of a join condition
and "JOIN" is defined to be equivalent to an "INNER JOIN"

Examples:

 SELECT * FROM customer AS c,
               orders   AS o
         WHERE c.custkey = o.custkey
 or (same):

 SELECT * FROM customer AS c,
          JOIN orders   AS o
         WHERE c.custkey = o.custkey
 or (same):

 SELECT * FROM customer AS c,
    INNER JOIN orders   AS o
         WHERE c.custkey = o.custkey

an example of 2 different ways of creating
the exact same OUTER JOIN (Left Join)

       $query = "SELECT *  FROM  categories01 as a
                      LEFT JOIN  categories02 as b
                             ON  a.cat01key = b.cat01key
                            AND  level01key = 2
                       ORDER BY  cat01title, cat02title " ;

       or (same result):

       $query = "SELECT *  FROM  categories01 as a
                      LEFT JOIN  categories02 as b
                          USING  (cat01key)
                          WHERE  level01key = 2
                       ORDER BY  cat01title, cat02title " ;

Here is a select statement to get a little information on an order in the NorthWind database (sample database with MS's Sequel Server 2000 and 2003). The numbers are rounded to 2 decimals. (the word "AS" (case insensitive) declares that you are renaming the field for this instance, but is not mandatory, just clearer and therefore, recommended)
( $order is a PHP variable whose value will be substituted at runtime )

      "SELECT  round(D.UnitPrice * Quantity, 2) AS Total,
               round(Discount, 2) AS Discount,
               round(D.UnitPrice * Quantity -
                     D.UnitPrice * Quantity * Discount, 2) AS Price
                    FROM nwCustomers as C,
                         nwOrders as O,
                         nwEmployees as E,
                         nwOrderDetails as D,
                         nwProducts as P
                   WHERE O.EmployeeID = E.EmployeeID
                     AND O.CustomerID = C.CustomerID
                     AND O.OrderID    = D.OrderID
                     AND D.ProductID  = P.ProductID
                     AND O.OrderID    = $order
                ORDER BY D.UnitPrice ";

Also, see northwind table definitions and data in other tutorials here.

left (outer) join: [not all posts have coaches but, some have several ]

  SELECT  *, COUNT(coachkey) as coaches
              FROM  ALposts  AS p
         LEFT JOIN  ALcoaches  AS  c
                ON  p.postnum = c.coALpost
          GROUP BY  p.postnum
          ORDER BY  p.poststate, p.postcity 

 if you just want to see those posts with coaches:
  SELECT  *, COUNT(coachkey) as coaches
              FROM  ALposts
         LEFT JOIN  ALcoaches
                ON  postnum = coALpost
          GROUP BY  postnum
            HAVING  coaches > 0
          ORDER BY  poststate, postcity
  (and remember HAVING goes between group and order; see below)

Note:
WHERE filters results before they are grouped (if)
HAVING filters results after they are grouped

example:
wrong:

   SELECT  COUNT(*) count,  plpostteam
     FROM  ALplayers
    WHERE  count > 10   /* <-------- error! */
 GROUP BY  plpostteam
 ORDER BY  count

 correct:
   SELECT  COUNT(*) count,  plpostteam
     FROM  ALplayers
 GROUP BY  plpostteam
   HAVING  count > 10   /* <-------- correct */
 ORDER BY  count

left join
to get those which have no matching record then,
WHERE h.orderid IS NULL
to get ONLY those with no matching record ... to get the "orphaned" records

 SELECT * FROM ordline AS l
     LEFT JOIN ordhead AS h
         USING ( orderid )
         WHERE h.total IS NULL
 or
 SELECT * FROM ordline AS l
     LEFT JOIN ordhead AS h
            ON l.orderid = h.orderid
         WHERE h.total IS NULL

Finding Duplicates: (1st, match on 1st initial and last name)

   SELECT  COUNT(*) as count, plNamef, plNamel,
           concat(substring(p1.plNamef, 1, 1), p1.plNamel) as ckname,
           plpaddress, plplyrid
     FROM  ALplayers
    WHERE  plgameyear = '2006'
 GROUP BY  ckname
   HAVING  count > 1
 ORDER BY  ckname

-- o.k. -- looks like 7 dups.(2 each)

now, to see more:
1st way: join table to itself.

   SELECT  DISTINCT p1.plplyrkey, p1.plplyrid, p1.plNamef, p1.plNamel,
           concat(substring(p1.plNamef, 1, 1), p1.plNamel) as ckname,
           p1.plpaddress, p1.plschlkey, p1.plpostteam
     FROM  ALplayers as p1
     JOIN  ALplayers as p2
    WHERE  p1.plgameyear = '2006'
      AND  p2.plgameyear = '2006'
      AND  concat(substring(p1.plNamef, 1, 1), p1.plNamel) =
           concat(substring(p2.plNamef, 1, 1), p2.plNamel)
      AND  p1.plplyrid != p2.plplyrid
 ORDER BY  ckname

--- ahha, 3 of the 7 are brothers or cousins with the same 1st initial.

now, to see those people again:
2nd way: sub-query. works in mysql 5.x (see sub-query topic)

   SELECT  p1.plplyrkey, p1.plplyrid, p1.plNamef, p1.plNamel,
           concat(substring(p1.plNamef, 1, 1), p1.plNamel) as ckname,
           p1.plpaddress, p1.plschlkey, p1.plpostteam
     FROM  ALplayers as p1
    WHERE  p1.plgameyear = '2006'
    HAVING  ckname  IN
          (SELECT  concat(substring(p2.plNamef, 1, 1), p2.plNamel) as p2ckname
             FROM  ALplayers as p2
            WHERE  p2.plgameyear = '2006'
         GROUP BY  p2ckname
           HAVING  count(*) > 1 )
   ORDER BY  ckname

Note:
WHERE filters results before they are grouped (if)
HAVING filters results after they are grouped

now, to see those who played in 2006 & 2007:

   SELECT  p1.plplyrid, p1.plNamef, p1.plNamel
     FROM  ALplayers as p1
     JOIN  ALplayers as p2
    WHERE  p1.plgameyear = '2006'
      AND  p2.plgameyear = '2007'
      AND  p1.plnamef = p2.plnamef
      AND  p1.plnamel = p2.plnamel
 ORDER BY  p1.plnamel, p1.plnamef