9, SQL Basic finishing (two tables connected Exists,join on,union)

The use of Exists

Select *from haha where exists (select *from bumen where Bumen.code = haha.bumen and bumen.name = ' Sales department ') and AGE>35

(Run method is query-by-clause)

Select Name,sex,age, (select name from bumen where Bumen.code = haha.bumen) as department from haha

Select Name,sex,age, (select name from bumen where Bumen.code = haha.bumen) as department, (select CEO from bumen where Bumen.code = Haha.bumen) as CEO from haha

A simple way to connect two tables:

1.

Select Haha.name,sex,age,bumen.name,ceo from Bumen,haha where haha.bumen = Bumen.code

2.join on (order can be reversed)

Select Haha.name,sex,age,bumen.name,ceo from haha

Join bumen on haha.bumen = Bumen.code

3.full usage of

INSERT into haha values (15, ' physical ', ' NV ', 34,5)

INSERT into bumen values (6, ' Security Department ', ' secure ', null,null)

Select Bumen.name,zhineng,ceo,haha.name,sex,age from haha

Full Join bumen on bumen.code = Haha.bumen

4 . use of left

In the case of no relationship, only all data from the table on the left side of the join is displayed, not the data from the right table

5 . The use of right

Ditto

(left and right are all sorted in the same way as all displayed data)

The use of Union

Connect the two columns, must meet the data type corresponding, with automatic deduplication function (according to pinyin or digital arrangement, to disrupt the original order)

select*from haha where code > 10

Union

select*from haha where code < 5

Select Name,bumen from haha where code > 10

Union

Select Ceo,code from Bumen

9, SQL Basic finishing (two tables connected Exists,join on,union)