直接上代码案例和截图:
mysql数据库中有a表和b表
a表
b表
案例1
SELECT a.id, a.`num`,b.`id`,b.`num`, a.num + b.num AS '相加'
FROM a,b WHERE a.id = b.id;
案例2
SELECT a.*, b.*,
CONCAT('a表num的',
a.num, ' + b表num的',
b.num),
a.num + b.num
FROM a,b WHERE a.id <> b.id;
案例3
SELECT b.id, b.num FROM b WHERE b.id NOT IN
(SELECT a.id FROM a,b WHERE a.id = b.id);
案例4
案例5
#
SELECT b.id, b.num FROM b WHERE b.id NOT IN(SELECT a.id FROM a WHERE 7 = 8);
案例6
SELECT b.id, b.num FROM b WHERE b.id IN
(SELECT a.id FROM a WHERE 7 = 8);
案例7
SELECT b.id, b.num FROM b WHERE b.id IN
(SELECT a.id FROM a WHERE a.id = b.id);
案例8
SELECT b.id, b.num FROM b
WHERE b.id = (SELECT a.id FROM a WHERE a.id = b.id);
案例9
SELECT b.id, b.num FROM b
WHERE b.id <> (SELECT a.id FROM a WHERE a.id = b.id);
案例10
SELECT b.id, b.num FROM b
WHERE b.id =(SELECT a.id FROM a WHERE a.id = b.id);
案例11
#Subquery returns more than 1 row
SELECT b.id, b.num FROM b
WHERE b.id = (SELECT a.id FROM a,b WHERE a.id = b.id);
案例12
SELECT b.id, b.num FROM b
WHERE b.id <> (SELECT a.id FROM a,b WHERE a.id = b.id);
案例13
SELECT b.id, b.num FROM b
WHERE b.id IN (SELECT a.id FROM a,b WHERE a.id = b.id);
案例14
SELECT b.id, b.num FROM b
WHERE b.id NOT IN (SELECT a.id FROM a,b WHERE a.id = b.id);
案例15
SELECT b.id, b.num, a.id, a.num
FROM a, b WHERE a.id <> b.id;
案例16
SELECT b.* FROM b
WHERE b.id NOT IN
(SELECT a.id FROM a, b WHERE a.id <> b.id);
案例17
SELECT b.* FROM b
WHERE b.id NOT IN
(SELECT b.id FROM a, b WHERE a.id <> b.id);
案例18
SELECT b.* FROM b
WHERE b.id
IN (SELECT a.id FROM a, b WHERE a.id <> b.id);
案例19
SELECT b.* FROM b
WHERE b.id
IN (SELECT b.id FROM a, b WHERE a.id <> b.id);
案例20
以下sql语句稍微难理解
SELECT b.id, b.num FROM b
WHERE b.id IN(SELECT a.id FROM a WHERE a.id <> b.id);
案例21
#Subquery returns more than 1 row
SELECT b.id, b.num FROM b
WHERE b.id =(SELECT a.id FROM a WHERE a.id <> b.id);
案例22
SELECT b.id, b.num FROM b
WHERE b.id IN(SELECT b.id FROM a WHERE a.id <> b.id);
案例23
#Subquery returns more than 1 row
SELECT b.id, b.num
FROM b WHERE b.id =(SELECT b.id FROM a WHERE a.id <> b.id);
案例24
SELECT a.*,
b.*,
CONCAT('a表num的', a.num, ' + b表num的', b.num),
a.num + b.num
FROM a,b WHERE a.id <> b.id;
案例25
SELECT b.id, b.num
FROM b
WHERE b.id NOT IN
(SELECT a.id FROM a WHERE a.id <> b.id);
案例26
#Subquery returns more than 1 row
SELECT b.id, b.num
FROM b
WHERE b.id <>(SELECT a.id FROM a WHERE a.id <> b.id);
案例27
SELECT b.id, b.num FROM b
WHERE b.id
NOT IN(SELECT b.id FROM a WHERE a.id <> b.id);
案例28
#Subquery returns more than 1 row
SELECT b.id, b.num
FROM b
WHERE b.id <>(SELECT b.id FROM a WHERE a.id <> b.id);
案例29
SELECT b.id, b.num
FROM b
WHERE b.id IN(SELECT a.id FROM a, b WHERE a.id <> b.id);
以下是完整的sql代码
#
SELECT * FROM a;
SELECT * FROM b;
#
SELECT a.*, b.*, CONCAT('a表num的', a.num, ' + b表num的', b.num), a.num + b.num FROM a,b WHERE a.id <> b.id;
#
SELECT b.id, b.num FROM b WHERE b.id NOT IN(SELECT a.id FROM a,b WHERE a.id = b.id);
SELECT b.id, b.num FROM b WHERE b.id NOT IN(SELECT a.id FROM a WHERE a.id = b.id);
#
SELECT b.id, b.num FROM b WHERE b.id NOT IN(SELECT a.id FROM a WHERE 7 = 8);
SELECT b.id, b.num FROM b WHERE b.id IN(SELECT a.id FROM a WHERE 7 = 8);
#
SELECT b.id, b.num FROM b WHERE b.id IN (SELECT a.id FROM a WHERE a.id = b.id);
SELECT b.id, b.num FROM b WHERE b.id = (SELECT a.id FROM a WHERE a.id = b.id);
SELECT b.id, b.num FROM b WHERE b.id <> (SELECT a.id FROM a WHERE a.id = b.id);
SELECT b.id, b.num FROM b WHERE b.id <> (SELECT a.id FROM a WHERE a.id = b.id);
SELECT b.id, b.num FROM b WHERE b.id =(SELECT a.id FROM a WHERE a.id = b.id);
#Subquery returns more than 1 row
SELECT b.id, b.num FROM b WHERE b.id = (SELECT a.id FROM a,b WHERE a.id = b.id);
SELECT b.id, b.num FROM b WHERE b.id <> (SELECT a.id FROM a,b WHERE a.id = b.id);
SELECT b.id, b.num FROM b WHERE b.id IN (SELECT a.id FROM a,b WHERE a.id = b.id);
SELECT b.id, b.num FROM b WHERE b.id NOT IN (SELECT a.id FROM a,b WHERE a.id = b.id);
SELECT b.id, b.num, a.id, a.num FROM a, b WHERE a.id <> b.id;
SELECT b.* FROM b WHERE b.id NOT IN (SELECT a.id FROM a, b WHERE a.id <> b.id);
SELECT b.* FROM b WHERE b.id NOT IN (SELECT b.id FROM a, b WHERE a.id <> b.id);
SELECT b.* FROM b WHERE b.id IN (SELECT a.id FROM a, b WHERE a.id <> b.id);
SELECT b.* FROM b WHERE b.id IN (SELECT b.id FROM a, b WHERE a.id <> b.id);
#以下sql语句稍微难理解
SELECT b.id, b.num FROM b WHERE b.id IN(SELECT a.id FROM a WHERE a.id <> b.id);
#Subquery returns more than 1 row
#SELECT b.id, b.num FROM b WHERE b.id =(SELECT a.id FROM a WHERE a.id <> b.id);
SELECT b.id, b.num FROM b WHERE b.id IN(SELECT b.id FROM a WHERE a.id <> b.id);
#Subquery returns more than 1 row
#SELECT b.id, b.num FROM b WHERE b.id =(SELECT b.id FROM a WHERE a.id <> b.id);
SELECT a.*, b.*, CONCAT('a表num的', a.num, ' + b表num的', b.num), a.num + b.num FROM a,b WHERE a.id <> b.id;
SELECT b.id, b.num FROM b WHERE b.id NOT IN(SELECT a.id FROM a WHERE a.id <> b.id);
#Subquery returns more than 1 row
#SELECT b.id, b.num FROM b WHERE b.id <>(SELECT a.id FROM a WHERE a.id <> b.id);
SELECT b.id, b.num FROM b WHERE b.id NOT IN(SELECT b.id FROM a WHERE a.id <> b.id);
#Subquery returns more than 1 row
#SELECT b.id, b.num FROM b WHERE b.id <>(SELECT b.id FROM a WHERE a.id <> b.id);
#
SELECT b.id, b.num FROM b WHERE b.id IN(SELECT a.id FROM a, b WHERE a.id <> b.id);
#
SELECT a.id, a.`num`,b.`id`,b.`num`, a.num + b.num AS '相加' FROM a,b WHERE a.id = b.id;