How to subtract two MySQL SELECT statements

Question

I have two mysql select statements and I want to subtract one from another:

First Select Statement:

SELECT rec.time_stamp,
       rec.day_chan1,
       rec.day_chan2,
       rec.day_chan3,
       rec.total_phase
FROM
  (SELECT ".$this->table_name.".*,
          DATE(time_stamp) AS mydate,
          TIME(time_stamp) AS mytime
   FROM ".$this->table_name."
   WHERE TIME(time_stamp) >= '00:00:00'
     AND TIME(time_stamp) <= '07:59:59') AS rec
JOIN
  (SELECT DATE(time_stamp) AS mydate,
          MAX(TIME(time_stamp)) AS mytime
   FROM ".$this->table_name."
   WHERE TIME(time_stamp) >= '00:00:00'
     AND TIME(time_stamp) <= '07:59:59'
   GROUP BY DATE(time_stamp)) AS max_times ON max_times.mydate = rec.mydate
AND max_times.mytime = rec.mytime
ORDER BY rec.mydate

Result:

+---------------------+-----------+-----------+-----------+-------------+
| time_stamp          | day_chan1 | day_chan2 | day_chan3 | total_phase |
+---------------------+-----------+-----------+-----------+-------------+
| 2014-06-03 07:59:48 |     24210 |     22744 |     26003 |       72957 |
| 2014-06-04 07:59:49 |    112603 |    103417 |    121368 |      337388 |
| 2014-06-05 07:59:50 |     21302 |     20165 |     23317 |       64784 |
| 2014-06-06 07:59:50 |     21561 |     20951 |     23875 |       66387 |
| 2014-06-07 07:59:03 |       408 |      1151 |       767 |        2326 |
| 2014-06-08 07:59:04 |       384 |      1151 |       767 |        2302 |
| 2014-06-09 07:59:53 |     21580 |     20656 |     23788 |       66024 |
| 2014-06-10 07:59:54 |     19689 |     18861 |     21657 |       60207 |
| 2014-06-11 07:59:54 |     22283 |     21132 |     24820 |       68235 |
| 2014-06-12 07:59:56 |     20489 |     19327 |     22377 |       62193 |
| 2014-06-13 07:59:56 |     20646 |     19485 |     22504 |       62635 |
| 2014-06-15 07:59:09 |       387 |      1151 |       767 |        2305 |
| 2014-06-16 07:59:58 |     30681 |     29050 |     33593 |       93324 |
| 2014-06-17 07:59:59 |     18248 |     17701 |     20432 |       56381 |
| 2014-06-18 07:37:49 |     40170 |     38123 |     47013 |      125306 |
| 2014-06-19 07:59:38 |     22417 |     21001 |     24484 |       67902 |
| 2014-06-20 07:59:50 |     19104 |     18238 |     21007 |       58349 |
| 2014-06-21 07:59:15 |       384 |      1151 |       767 |        2302 |
| 2014-06-22 07:59:16 |       384 |      1151 |       767 |        2302 |
| 2014-06-23 07:59:53 |     19675 |     18582 |     21114 |       59371 |
| 2014-06-24 07:59:42 |     18341 |     17504 |     19427 |       55272 |
| 2014-06-25 07:59:55 |     19554 |     18469 |     20879 |       58902 |
| 2014-06-26 07:59:55 |     20241 |     19262 |     21881 |       61384 |
| 2014-06-27 07:59:56 |     17984 |     17033 |     19528 |       54545 |
| 2014-06-28 07:59:21 |       394 |      1151 |       767 |        2312 |
| 2014-06-29 07:59:22 |       406 |      1151 |       767 |        2324 |
| 2014-06-30 07:59:58 |     18909 |     18012 |     20306 |       57227 |
| 2014-07-01 07:59:59 |     17736 |     16951 |     19213 |       53900 |
| 2014-07-02 07:59:48 |     19088 |     18310 |     21040 |       58438 |
| 2014-07-03 07:59:49 |     19842 |     19024 |     21490 |       60356 |
+---------------------+-----------+-----------+-----------+-------------+

Second Select Statement:

SELECT rec.time_stamp,
       rec.day_chan1,
       rec.day_chan2,
       rec.day_chan3,
       rec.total_phase
FROM
  (SELECT ".$this->table_name.".*,
          DATE(time_stamp) AS mydate,
          TIME(time_stamp) AS mytime
   FROM ".$this->table_name."
   WHERE TIME(time_stamp) >= '00:00:00'
     AND TIME(time_stamp) <= '07:59:59') AS rec
JOIN
  (SELECT DATE(time_stamp) AS mydate,
          MAX(TIME(time_stamp)) AS mytime
   FROM ".$this->table_name."
   WHERE TIME(time_stamp) >= '00:00:00'
     AND TIME(time_stamp) <= '07:59:59'
   GROUP BY DATE(time_stamp)) AS max_times ON max_times.mydate = rec.mydate
AND max_times.mytime = rec.mytime
ORDER BY rec.mydate

Result:

+---------------------+-----------+-----------+-----------+-------------+
| time_stamp          | day_chan1 | day_chan2 | day_chan3 | total_phase |
+---------------------+-----------+-----------+-----------+-------------+
| 2014-06-03 07:59:48 |     24210 |     22744 |     26003 |       72957 |
| 2014-06-04 07:59:49 |    112603 |    103417 |    121368 |      337388 |
| 2014-06-05 07:59:50 |     21302 |     20165 |     23317 |       64784 |
| 2014-06-06 07:59:50 |     21561 |     20951 |     23875 |       66387 |
| 2014-06-07 07:59:03 |       408 |      1151 |       767 |        2326 |
| 2014-06-08 07:59:04 |       384 |      1151 |       767 |        2302 |
| 2014-06-09 07:59:53 |     21580 |     20656 |     23788 |       66024 |
| 2014-06-10 07:59:54 |     19689 |     18861 |     21657 |       60207 |
| 2014-06-11 07:59:54 |     22283 |     21132 |     24820 |       68235 |
| 2014-06-12 07:59:56 |     20489 |     19327 |     22377 |       62193 |
| 2014-06-13 07:59:56 |     20646 |     19485 |     22504 |       62635 |
| 2014-06-15 07:59:09 |       387 |      1151 |       767 |        2305 |
| 2014-06-16 07:59:58 |     30681 |     29050 |     33593 |       93324 |
| 2014-06-17 07:59:59 |     18248 |     17701 |     20432 |       56381 |
| 2014-06-18 07:37:49 |     40170 |     38123 |     47013 |      125306 |
| 2014-06-19 07:59:38 |     22417 |     21001 |     24484 |       67902 |
| 2014-06-20 07:59:50 |     19104 |     18238 |     21007 |       58349 |
| 2014-06-21 07:59:15 |       384 |      1151 |       767 |        2302 |
| 2014-06-22 07:59:16 |       384 |      1151 |       767 |        2302 |
| 2014-06-23 07:59:53 |     19675 |     18582 |     21114 |       59371 |
| 2014-06-24 07:59:42 |     18341 |     17504 |     19427 |       55272 |
| 2014-06-25 07:59:55 |     19554 |     18469 |     20879 |       58902 |
| 2014-06-26 07:59:55 |     20241 |     19262 |     21881 |       61384 |
| 2014-06-27 07:59:56 |     17984 |     17033 |     19528 |       54545 |
| 2014-06-28 07:59:21 |       394 |      1151 |       767 |        2312 |
| 2014-06-29 07:59:22 |       406 |      1151 |       767 |        2324 |
| 2014-06-30 07:59:58 |     18909 |     18012 |     20306 |       57227 |
| 2014-07-01 07:59:59 |     17736 |     16951 |     19213 |       53900 |
| 2014-07-02 07:59:48 |     19088 |     18310 |     21040 |       58438 |
| 2014-07-03 07:59:49 |     19842 |     19024 |     21490 |       60356 |
+---------------------+-----------+-----------+-----------+-------------+

now I need to subtract first - second statement.

I tried this but it did not work:

SELECT *
FROM (
        (SELECT rec.time_stamp,
                rec.day_chan1,
                rec.day_chan2,
                rec.day_chan3,
                rec.total_phase
         FROM
           (SELECT ".$this->table_name.".*,
                   DATE(time_stamp) AS mydate,
                                       TIME(time_stamp) AS mytime
            FROM ".$this->table_name."
            WHERE TIME(time_stamp) >= '08:00:00'
              AND TIME(time_stamp) <= '16:59:59') AS rec
         JOIN
           (SELECT DATE(time_stamp) AS mydate,
                                       MAX(TIME(time_stamp)) AS mytime
            FROM ".$this->table_name."
            WHERE TIME(time_stamp) >= '08:00:00'
              AND TIME(time_stamp) <= '16:59:59'
            GROUP BY DATE(time_stamp)) AS max_times ON max_times.mydate = rec.mydate
         AND max_times.mytime = rec.mytime
         ORDER BY rec.mydate) -
        (SELECT rec.time_stamp,
                rec.day_chan1,
                rec.day_chan2,
                rec.day_chan3,
                rec.total_phase
         FROM
           (SELECT ".$this->table_name.".*,
                   DATE(time_stamp) AS mydate,
                                       TIME(time_stamp) AS mytime
            FROM ".$this->table_name."
            WHERE TIME(time_stamp) >= '00:00:00'
              AND TIME(time_stamp) <= '07:59:59') AS rec
         JOIN
           (SELECT DATE(time_stamp) AS mydate,
                                       MAX(TIME(time_stamp)) AS mytime
            FROM ".$this->table_name."
            WHERE TIME(time_stamp) >= '00:00:00'
              AND TIME(time_stamp) <= '07:59:59'
            GROUP BY DATE(time_stamp)) AS max_times ON max_times.mydate = rec.mydate
         AND max_times.mytime = rec.mytime
         ORDER BY rec.mydate))

See follow questions [Select from select](http://stackoverflow.com/questions/12506610/mysql-select-from-select) — Rinat Mukhamedgaliev, Jul 15 '14 at 07:39
`NOT IN` is your keyword. http://stackoverflow.com/questions/1519272/mysql-not-in-query — Aleksei Matiushkin, Jul 15 '14 at 07:48
The question is about arithmetic subtraction, not excluding fields from field lists. — peter_the_oak, Jul 15 '14 at 07:56
Have a look at this picture: http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg And read the corresponding link: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins You want the `FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.id IS NULL` variation. — nl-x, Jul 15 '14 at 07:58
@peter_the_oak I don't think it's about arithmetic subtraction, but relational subtraction. — Kousalik, Jul 15 '14 at 07:59
@mudasobwa: no. I am very proficient in both, but I cringe every time i have to use mysql — mvp, Jul 15 '14 at 08:08

Kousalik · Answer 1 · 2014-07-15T21:54:16.820

2

Use left join instead as explained: here

Unfortunatelly MySQL doesn't have an operator for relational subtraction like some other DBMS do, so you should something like explained in following example:

Solution Oracle

SELECT x, y FROM table_a
MINUS
SELECT x, y FROM table_b;

Solution MySQL

SELECT a.x, a.y
FROM table_a a LEFT JOIN table_b b
ON a.x = b.x AND a.y = b.y
WHERE b.x IS NULL;

edited Jul 15 '14 at 21:54

answered Jul 15 '14 at 07:52

Kousalik

3,111
3
24
46

3

Note that `MINUS` is Oracle-only. For all other SQL99 compliant databases (MS SQL, PostgreSQL, DB2, etc) this operator is called `EXCEPT`. – mvp Jul 15 '14 at 08:11
Sorry - poorly choosed words. – Kousalik Jul 15 '14 at 21:50

How to subtract two MySQL SELECT statements

1 Answers1