sql连接查询

建表语句

CREATE TABLE  A (

  id int  NOT NULL,

  name varchar(255)

)

INSERT INTO A VALUES (1, 'zhangsan')

INSERT INTO A VALUES (2, 'lisi')

INSERT INTO A VALUES (3, 'wangmangzi')

INSERT INTO A VALUES (4, 'JAck')

CREATE TABLE  B (

  id int  NOT NULL,

  name varchar(255),

  age int

)

INSERT INTO B VALUES (1, 'zhangsan', 16)

INSERT INTO B VALUES (2, 'lisi', 14)

INSERT INTO B VALUES (3, 'rose', 13)

INSERT INTO B VALUES (4, 'nike', 12)

 

inner join

SELECT * FROM A INNER JOIN B ON A.name= B.name  

从查询结果可以看出查询结果为A与B的交集

left join

SELECT * FROM A LEFT JOIN B ON A.name= B.name

从查询结果可以看出查询到的是A的全集，B中存在则有值，没有则为null

同理right join查询的就是B的全集，A中存在则有值，没有则为null

 

full join

SELECT * FROM A FULL JOIN B ON A.name= B.name

从查询结果可以看出，结果为A，B的并集。不存在的则为null

SELECT * FROM A FULL JOIN B ON A.name= B.name where A.id is null or B.id is null

可与去除A和B的交集