Database-T-SQL Statement-Advanced query

Source: Internet
Author: User

1. Connection query (extension of the column, the two tables are connected to display together) (the data volume is large, less use of such queries, slow, will form a Cartesian product)

The first form of

SELECT * FROM Info,nation #会笛卡尔积

SELECT * from info,nation where info.nation = Nation.code #加上筛选条件

Select Info.code,,sex,,birthday from info,nation where info.nation = Nation.code #查询指定列 If two tables have the same column name , you need to add a table name such as Info.code, if not the same name, you can directly write the column names

Select Info.code as ' codename ', as ' name ', sex as ' gender ', as ' national ', Birthday as ' birthday ' from Info,na tion where info.nation = Nation.code #改表头 after the column name with AS ' ' Change the table header

The second form of:

SELECT * from Info join Nation #会形成笛卡尔积

SELECT * from Info join Nation on info.nation = Nation.code # Join in on after add filter condition

2. Federated queries (expansion of rows)

SELECT * from Info where Nation = ' n002 '


SELECT * from Info where Code = ' p002 '

3. Subquery: In an SQL statement, there are at least two queries, one of the results of a query as another B's query condition A is called the inner query or subquery, B is called the outer query or the parent query

3.1 Unrelated subqueries

Unrelated subquery features: subqueries can be executed separately

Query people for "Han" personnel information

SELECT * from Info where Nation = (select Code from Nation where Name = ' Han ')

SELECT * from Info where Nation in (select Code from Nation where name = ' Han ' or name = ' hui ') #结果不止一条时 with in connection. not is in the query results and in the opposite.

3.2 Related subqueries

Inquire about the same series of vehicles with lower fuel consumption than average fuel consumption

Select AVG (oil) from Car where Brand = "

SELECT * from Car where oil < average fuel consumption

SELECT * from Car a where A.oil < (select AVG (b.oil) from car b where B.brand = A.brand)

Database-T-SQL Statement-Advanced query

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.