Difference between revisions of "Forgotten rows"

Revision as of 13:05, 30 July 2012

Include the rows your JOIN forgot.

schema:scott

DROP TABLE customer;
DROP TABLE invoice;

 CREATE TABLE customer(
  id INTEGER,
  name VARCHAR(20));
INSERT INTO customer VALUES (1,'Betty');
INSERT INTO customer VALUES (2,'Robert');
INSERT INTO customer VALUES (3,'Janette');
CREATE TABLE invoice (
  invoiceno INTEGER,
  whn DATE,
  custid INTEGER,
  cost INTEGER );
INSERT INTO invoice VALUES (1,(2006,11,01),1,100);
INSERT INTO invoice VALUES (2,(2006,11,05),1,500);
INSERT INTO invoice VALUES (3,(2006,11,11),3,200);

The following query will only give two rows as the JOIN function automatically does not include rows with a count of 0.

SELECT name, COUNT(*)
FROM customer JOIN invoice ON (id=custid)
GROUP BY name

In order to obtain the rows where the count from the query is 0 a LEFT JOIN or a UNION can be used.

 SELECT name, COUNT(*)
 FROM customer LEFT JOIN invoice ON (id=custid)

@@ Line 27: / Line 27: @@
 <p>In order to obtain the rows where the count from the query is 0 a
 LEFT JOIN or a UNION can be used.</p>
- SELECT name, COUNT(*)
- FROM customer LEFT JOIN invoice ON (id=custid)
 </div>
-<source lang='sql' class='def'>SELECT name, COUNT(*)
+<source lang='sql' class='def'> SELECT name, COUNT(*)
-FROM customer JOIN invoice ON (id=custid)
+ FROM customer LEFT JOIN invoice ON (id=custid)</source>
-UNION
-SELECT name, 0
-FROM customer
-WHERE id NOT IN (SELECT custid FROM invoice)</source>
 <div class="ecomm e-mysql" style="display: none"></div>
 <div class="ecomm e-oracle" style="display: none"></div>
 </div>
 {{Hacks Ref}}

Difference between revisions of "Forgotten rows"

Revision as of 13:05, 30 July 2012

Personal tools

Namespaces

Variants

Views

Actions

Search

Reference

Toolbox

Google AdSense