Use of select (2)

Multi-Table query

/* Basic connection */select. name, B. namefrom T_Employee a, T_Department bwhere. required mentid = B. id/* inner connection to remove items that do not match the connection condition */select. name, B. namefrom T_Employee a inner join T_Department bon. required mentid = B. idselect. name, B. namefrom T_Employee a inner join T_Department bon. required mentid = B. id and B. isDelete = 1 select. name, B. namefrom T_Employee a inner join T_Department bon. required mentid = B. id where B. isDelete = 1 select. name, B. namefrom T_Employee a join T_Department B -- inner can be omitted on. required mentid = B. id where B. isDelete = 1/* left Outer Join. Items in the left table that do not match the join condition are retained */select. name, B. namefrom T_Employee a left outer join T_Department B -- outer can be omitted on. required mentid = B. idselect. name, B. namefrom T_Employee a left outer join T_Department B -- outer can be omitted on. required mentid = B. id and B. isDelete = 1 -- the content of Table B is displayed only when the connection condition is met. select. name, B. namefrom T_Employee a left outer join T_Department B -- outer can be omitted on. required mentid = B. idwhere B. isDelete = 1 -- it is equivalent to executing the first three rows, then filtering with the where statement/* Right Outer Join, retaining the items in the right table that do not match the connection condition */select. name, B. namefrom T_Employee a right outer join T_Department B -- outer can be omitted on. required mentid = B. id select. name, B. namefrom T_Employee a right outer join T_Department B -- outer can be omitted on. required mentid = B. id and B. isDelete = 1 -- the content of table a is displayed only when the connection condition is met. select. name, B. namefrom T_Employee a right outer join T_Department B -- outer can be omitted on. required mentid = B. idwhere B. isDelete = 1 -- it is equivalent to executing the first three rows first, then filtering with the where statement/* Full join, listing all the items in the left table first, retain the items in the left and right tables that do not match the connection conditions */select. name, B. namefrom T_Employee a full outer join T_Department B -- outer can be omitted on. required mentid = B. id select. name, B. namefrom T_Employee a full outer join T_Department B -- outer can be omitted on. required mentid = B. id and B. isDelete = 1 -- list all items in the left table first, and retain the items in the left and right tables that do not match the connection conditions select. name, B. namefrom T_Employee a full outer join T_Department B -- outer can be omitted on. required mentid = B. idwhere B. isDelete = 1 -- equivalent to executing the first three rows first, and then filtering with the where statement/* cross join, the fields in table B of select are used to match all the fields in Table a of select */select. name, B. namefrom T_Employee a cross join T_Department bselect. name, B. namefrom T_Employee a cross join T_Department bwhere B. isDelete = 1 -- it is equivalent to executing the first two rows, then filtering/* Self-join with the where statement, and connecting to the same table */select. name,. isDelete, B. namefrom T_Department a, T_Department bwhere. isDelete = B. isDeleteselect. name,. isDelete, B. namefrom T_Department a, T_Department bwhere. isDelete = B. isDelete and. id <> B. id and. name = 'guangdong province'

 

/* The Union query connects the two query results, so the number of select statements must be the same, the type must be convertible to the same one, and the placeholder value can be empty, union all indicates that all columns are displayed. Even if there are duplicates, union indicates that only non-duplicated information is displayed */select. name,. baseSalary, B. namefrom T_Employee a, T_Department bwhere. required mentid = B. id and. genderId = '403a0bd5-A304-4981-9535-ADA7AF2BFB51 'unionselect', SUM (. baseSalary), ''from T_Employee a, T_Department bwhere. required mentid = B. id and. genderId = '403a0bd5-A304-4981-9535-ADA7AF2BFB51 'unionselect', AVG (. baseSalary), ''from T_Employee a, T_Department bwhere. required mentid = B. id and. genderId = '403a0bd5-A304-4981-9535-ADA7AF2BFB51 'order by BaseSalary/* subquery */select. name,. baseSalary, B. namefrom T_Employee a, T_Department bwhere. required mentid = B. id and. genderId = '403a0bd5-A304-4981-9535-ADA7AF2BFB51 'and. baseSalary <(select AVG (. baseSalary) + 1 from T_Employee a, T_Department bwhere. required mentid = B. id and. genderId = '403a0bd5-A304-4981-9535-ADA7AF2BFB51 ')/* subquery nesting */select. name,. baseSalary, B. namefrom T_Employee a, T_Department bwhere. required mentid = B. id and. genderId = '403a0bd5-A304-4981-9535-ADA7AF2BFB51 'and. name in (select Namefrom T_Employee a, T_SalarySheet bwhere. required mentid = B. using mentidand B. year = (select Year from T_SalarySheet where Id = 'e0ad51de-C6A2-4BBE-BF85-D978EAEF9BE9 ')/* exists if exists has results, start querying the master select */select. name,. baseSalary, B. namefrom T_Employee a, T_Department bwhere exists (select * from T_Employee where Name = 'tags') declare @ address varchar (20) declare @ pwd varchar (10) set @ address = 'It tells 'set @ pwd =' if exists (select * from T_Employee a where. address = @ address and. name = @ pwd) print 'logon successful elseprint' logon failed'/* submit the query to obtain the intersection part */select. name,. baseSalaryfrom T_Employee aintersectselect. name,. baseSalary from T_Employee a where Name = 'all'/* difference query, remove the intersection of the two in the first select */select. name,. baseSalaryfrom T_Employee aexceptselect. name,. baseSalary from T_Employee a where Name = 'tags'