The intersection of MySQL and the difference set

A question seen in the forum is summarized here below:
CREATE TABLEconsume(
idVARCHAR (one) is not NULL,
tidVARCHAR (one) not NULL
)
Collate= ' Utf8_general_ci '
Engine=myisam
;
INSERT intoconsume(id,tidVALUES (' 1 ', ' 11 ');
INSERT intoconsume(id,tidVALUES (' 2 ', ' 14 ');
INSERT intoconsume(id,tidVALUES (' 3 ', ' 12 ');

CREATE TABLEteacher(
idVARCHAR (one) is not NULL,
tnameVARCHAR (one) is not NULL,
tdateDATETIME not NULL
)
Collate= ' Utf8_general_ci '
Engine=myisam
;
INSERT intoteacher(id,tname,tdateVALUES (' 10 ', ' Miss Li ', ' 2008-01-22 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 11 ', ' Teacher ', ' 2008-01-21 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 13 ', ' Teacher song ', ' 2008-01-28 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 14 ', ' Teacher Wei ', ' 2008-01-29 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 15 ', ' Teacher Kim ', ' 2008-01-30 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 16 ', ' Miss Zhao ', ' 2008-01-19 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 17 ', ' Teacher Zhang ', ' 2008-01-18 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 18 ', ' Teacher Yan ', ' 2008-01-17 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 12 ', ' Miss Gong ', ' 2008-01-25 21:54:27 ');
INSERT intoteacher(id,tname,tdateVALUES (' 19 ', ' Teacher Liu ', ' 2008-01-17 21:34:27 ');

Please
If the ID in teacher is in the TID in consume, it's in front, not in the back row.
For the row in front and follow the ID in consume ascending
For the rows that follow, sort by the descending order of tdate in teacher

For this actually split out:
1, the ID in the teacher in the consume in the TID in accordance with the ID in the consume ascending row
Select t.* from teacher T joins consume C on c.tid= t.id ORDER by c.id ASC
2. The ID in teacher is not sorted by tdate in teacher in the TID in consume
Select t.* from teacher T left joins consume C on c.tid= t.id where c.id are NULL order by tdate Desc
and connect two sheets.
SELECT * FROM (select t.* to teacher T join consume C on c.tid= t.id ORDER by c.id ASC) b
Union
SELECT * FROM (select t.* to teacher T left joins consume C on c.tid= t.id where c.id are NULL order by tdate Desc) A

650) this.width=650; "src=" http://img.blog.csdn.net/20150826231019004 "alt=" here write a picture describing "title=" "style=" border:none; "/ >
Here's a description of the method under 2.
650) this.width=650; "src=" http://img.blog.csdn.net/20150826230821063 "alt=" The first picture "title=" "style=" border:none; "/ >650) this.width=650; "src=" http://img.blog.csdn.net/20150826230935879 "alt=" the second picture "title=" "style=" Border:none; " />
Like these two pictures, from the data look more like the first picture
First the left join can find all the data for the teacher table (SELECT * FROM teacher T left join consume C on c.tid= t.id ORDER BY tdate Desc),
650) this.width=650; "src=" http://img.blog.csdn.net/20150826231536364 "alt=" here write a picture describing "title=" "style=" border:none; "/ >
Here you can see that all we need is the ID no data part, so add a condition c.id is null after the left join to get this difference set
Similar to the first picture
So now how to get the second picture, here is actually the same, because the left join can get the data is the teacher table of all the data
650) this.width=650; "src=" http://img.blog.csdn.net/20150826232225113 "alt=" The third picture "title=" "style=" border:none; "/ >
So I can get rid of that part of the intersection (C.id is not NULL), so add a condition c.id is null after the left join, like the second picture
The above is my understanding of the intersection and the difference set.
Reference: http://blog.itpub.net/29510932/viewspace-1777673/
http://blog.csdn.net/youngqj/article/details/7225400

The intersection of MySQL and the difference set