mysql无关联连接_MYSQL查询没有关联的数据表的记录(LEFT JOIN ON)

MYSQL查询没有关联的数据表的记录(LEFT JOIN ON)

注意与数据表关联之间的区别

A表

tid

subject

create table A(tid int(6) NOT NULL AUTO_INCREMENT UNIQUE,subject VARCHAR(20));

INSERT INTO A(subject) VALUES('a');

INSERT INTO A(subject) VALUES('b');

INSERT INTO A(subject) VALUES('c');

INSERT INTO A(subject) VALUES('d');

SELECT * FROM A; threads

+-----+---------+

| tid | subject |

+-----+---------+

|   1 | a       |

|   2 | b       |

|   3 | c       |

|   4 | d       |

+-----+---------+

B表

tid

name

create table B(tid int(6),name VARCHAR(20));

INSERT INTO B(tid,name) VALUES(1,'aa');

INSERT INTO B(tid,name) VALUES(1,'bb');

INSERT INTO B(tid,name) VALUES(1,'cc');

INSERT INTO B(tid,name) VALUES(1,'dd');

INSERT INTO B(tid,name) VALUES(2,'q');

INSERT INTO B(tid,name) VALUES(2,'w');

SELECT * FROM B; threadtags

+------+------+

| tid  | name |

+------+------+

|    1 | aa   |

|    1 | bb   |

|    1 | cc   |

|    1 | dd   |

|    2 | q    |

|    2 | w    |

+------+------+

两种方案：

第一种：SELECT tid,subject FROM A WHERE tid not in(SELECT tid FROM B);

第二种：SELECT t.tid,t.subject FROM A t LEFT JOIN B tt ON t.tid=tt.tid where tt.tid is null;

+-----+---------+

| tid | subject |

+-----+---------+

|   3 | c       |

|   4 | d       |

+-----+---------+

国际日期变更线

create table C(tid int(6) NOT NULL AUTO_INCREMENT UNIQUE,name VARCHAR(20),dateline int(10));

mysql> describe C;

+----------+-------------+------+-----+---------+----------------+

| Field    | Type        | Null | Key | Default | Extra          |

+----------+-------------+------+-----+---------+----------------+

| tid      | int(6)      | NO   | PRI | NULL    | auto_increment |

| name     | varchar(20) | YES  |     | NULL    |                |

| dateline | int(10)     | YES  |     | NULL    |                |

+----------+-------------+------+-----+---------+----------------+

INSERT INTO C(name,dateline) VALUES('qinmi',UNIX_TIMESTAMP(now()));

SELECT * FROM C;

mysql> SELECT * FROM C;

+-----+-------+------------+

| tid | name  | dateline   |

+-----+-------+------------+

|   1 | qinmi |       2011 |

|   2 | qinmi | 1300244856 |

+-----+-------+------------+

2 rows in set (0.00 sec)

UNIX_TIMESTAMP(now()) UNIX时间戳，这样的表示形式虽然不直观，不过也少了在MSSQL中比较排序等的问题了，因为是整型值的比较，很简单。