Well, apparently, based on the priorities for another answer, this requires further explanation. Example (runs with MySQL, because it’s convenient for me, but the principle is universal for any SQL dialect):
CREATE TABLE Blah (
ID INT PRIMARY KEY,
SomeText VARCHAR(30),
ParentID INT
)
INSERT INTO Blah VALUES (1, 'One', 0);
INSERT INTO Blah VALUES (2, 'Two', 0);
INSERT INTO Blah VALUES (3, 'Three', 1);
INSERT INTO Blah VALUES (4, 'Four', 1);
INSERT INTO Blah VALUES (5, 'Five', 4);
Left attachment version:
SELECT a.ID, a.SomeText, COUNT(1)
FROM Blah a
JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText
Wrong. Ignores the case without children.
Left outer join:
SELECT a.ID, a.SomeText, COUNT(1)
FROM Blah a
LEFT OUTER JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText
Wrong, and the reason is a little subtle. COUNT(1)counts NULLlines, while COUNT(b.ID)not. So this is wrong, but it is correct:
SELECT a.ID, a.SomeText, COUNT(b.ID)
FROM Blah a
LEFT OUTER JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText
Associated Subquery:
SELECT ID, SomeText, (SELECT COUNT(1) FROM Blah WHERE ParentID= a.ID) ChildCount
FROM Blah a
Also correct.
Ok, so what to use? Plans only tell you. The question of subqueries and left joins is old and there is no clear answer without comparing it. Therefore, we need some data:
<?php
ini_set('max_execution_time', 180);
$start = microtime(true);
echo "<pre>\n";
mysql_connect('localhost', 'scratch', 'scratch');
if (mysql_error()) {
echo mysql_error();
exit();
}
mysql_select_db('scratch');
if (mysql_error()) {
echo mysql_error();
exit();
}
$count = 0;
$limit = 1000000;
$this_level = array(0);
$next_level = array();
while ($count < $limit) {
foreach ($this_level as $parent) {
$child_count = rand(0, 3);
for ($i=0; $i<$child_count; $i++) {
$count++;
query("INSERT INTO Blah (ID, SomeText, ParentID) VALUES ($count, 'Text $count', $parent)");
$next_level[] = $count;
}
}
$this_level = $next_level;
$next_level = array();
}
$stop = microtime(true);
$duration = $stop - $start;
$inserttime = $duration / $count;
echo "$count users added.\n";
echo "Program ran for $duration seconds.\n";
echo "Insert time $inserttime seconds.\n";
echo "</pre>\n";
function query($query) {
mysql_query($query);
if (mysql_error()) {
echo mysql_error();
exit();
}
}
?>
(32M) , 876 109 , , . , Oracle SQL Server, Oracle XE SQL Server Express 2005.
, count . , . MySQL, , . Oracle. SQL Server .
, : SELECT COUNT(1) FROM ( ... ), - raw.
:
- MySQL 5.0 PremiumSoft Navicat (
LIMIT 10000 ); - SQL Server Express 2005 Microsoft SQL Server Management Studio Express;
- Oracle XE PL/SQL Developer 7 ( 10 000 ).
:
SELECT a.ID, a.SomeText, COUNT(b.ID)
FROM Blah a
LEFT OUTER JOIN Blah b ON a.ID= b.ParentID
GROUP BY a.ID, a.SomeText
- MySQL: 5.0: 51.469s/49.907s
- SQL Server: 0 (1)/9s (2)
- Oracle XE: 1.297s/2.656s
(1) ( )
(2) , , , 10000
. , SomeText MySQL. 10000 MySQL ( 4-5 ). Oracle , PL/SQL Developer , 100- .
:
SELECT ID, SomeText, (SELECT COUNT(1) FROM Blah WHERE ParentID= a.ID) ChildCount
FROM Blah a
- MySQL: 8.844s/11.10s
- SQL Server: 0s/6s
- Oracle: 0.046s/1.563s
, MySQL 4-5 , Oracle , SQL Server, , .
: , .
, . , , . : -, , , . :
SELECT id,
(SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'UNPAID') unpaid_invoices,
(SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'OVERDUE') overdue_invoices,
(SELECT COUNT(1) FROM invoices WHERE customer_id = c.id AND status = 'PAID') paid_invoices
FROM customers c
.
, , , . RDBMS .