mysql中子查询案例

直接上代码案例和截图：

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;