1T-SQL advanced query Statement2 3 Advanced Query4 51. Connection query,extensions to the result set column6SELECT *From info7 8SELECT * FROM Info,nation#To form a Cartesian product9SELECT * from Info,nation where info.nation=nation.CodeTenSelect Info.code,info.name,sex,nation.name,birthday from Info,nation where info.nation=nation.Code One ASELECT * FROM InfoJoinNation on Info.nation=nation.Code - -2. Joint queries,extensions to result set rows theSelect Code,name from Info - Union -Select Code,name from Nation
13.Sub-query2 3 Parent Query: Outer query4 Sub-query: Inner query5 6 The result of the subquery is a condition of the parent query7 8(1) unrelated subqueries9 The subquery does not have a relationship with the parent query at execution time, and the subquery can be executed separatelyTen One1.Query the People for ' Han ' all personnel information AParent query: SELECT * FROM info where nation=() -Subquery: Select code from Nation where Name= ' Han ' - theSELECT * FROM info where nation= (select code from Nation where Name= ' Han ')
1 2. Find all car information in the series named ' BMW 5 system ' 2 select * from car where brand= (select Brand_Code from brand where Brand_name= ' BMW 5 Series ')
1 (2) related subquery 2 The subquery has a relationship with the parent query at execution time, and the subquery cannot execute 34 1 alone. Find all cars with fuel consumption less than the average fuel consumption in the car table 5 Parent query: SELECT * from car where oil<(average fuel consumption)6 sub-query: Select AVG (oil) from car where brand= the Series 78 as as B where B.brand=a.brand)
T-SQL Advanced query statement (parent-child query)