updated 2019-04-20
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
JOIN orders AS o
USING (custkey)
an example of 2 different ways of creating
the exact same OUTER JOIN (Left Join)
"SELECT * FROM categories01 as a
LEFT JOIN categories02 as b
ON a.cat01key = b.cat01key
AND a.level01key = 2
ORDER BY cat01title, cat02title "
or (same result):
"SELECT * FROM categories01 as a
LEFT JOIN categories02 as b
USING (cat01key)
WHERE a.level01key = 2
ORDER BY cat01title, cat02title " ;
NOTE: a LEFT (outer) join will not work as expected if fields in the LEFT JOINed table are included in the WHERE criteria. The results will be the same as a standard (inner) JOIN. Records must be found in both tables.
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 and a tmestamp was added to nwOrders. (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,
substring(CAST(O.timestamp AS CHAR), 1, 16) AS date_time
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 examples below:
to get a count of records by group
[if you need to be more specific about which records,
see “MySQL: Some complex syntax”]
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
HAVING goes between group by and order by
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
GROUP BY plpostteam
ORDER BY count
correct:
SELECT COUNT(*) count, plpostteam
FROM ALplayers
GROUP BY plpostteam
HAVING count > 10
ORDER BY count
When grouping, “order by” takes effect after the grouping has been done and within any group, the first, oldest, record is taken and it goes on to find the next group.
If you want a specific record within a group other than the first (oldest) you will have to order the records before arriving at the “GROUP BY” statement.
For instance, if you want the last (most recent) record,
from a table “customers” where “id” is the auto-incremented key and there is “name” field you want to group by
your query might look like this:
select * from (select * from customers ORDER BY id DESC) AS x GROUP BY name
Case
When column1 < 0 Then column2
When column1 = 0 Then column3
When column1 > 0 Then column2 + column3
Else column3 End
fields: a, b, c : select the largest
CASE
WHEN a > b
AND a > c
THEN a
WHEN b > a
AND b > c
THEN b
ELSE c END AS largest_abc
SELECT * FROM `mdl_lesson`
WHERE ts > '2013-10-11 00:00:00'
AND courseid IN (614, 616, 2062, 1392, ...)
get a record count for each dept.:
SELECT location,
SUM(IF(dept = "Development", 1,0)) AS `Development` ,
SUM(IF(dept = "Personnel", 1,0)) AS `Personnel`,
SUM(IF(dept = "Research", 1,0)) AS `Research`,
SUM(IF(dept = "Sales", 1,0)) AS `Sales`,
SUM(IF(dept = "Training", 1,0)) AS `Training`,
COUNT(*) AS total
FROM locations INNER JOIN employees USING (loc_code)
INNER JOIN departments USING (dept_code)
GROUP BY location;
order by ... the timestamp field which is more current, with the most current on top:
ORDER BY IF(u.timestamp1 > u.timestamp2, u.timestamp1, u.timestamp2 ) DESC,
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
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
[ != will often work for <> but is not an ISO standard ]
--- ahha, 3 of the 7 are brothers or cousins with the same 1st initial.
to see those people again:
2nd way: sub-query. (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