1. Multi-Table Query
1) Descartes Set:
- SELECT * FROM table name 1, table name 2
- SELECT * FROM table name 1, table name 2 where table name 1. field name = Table Name 2. Field Name
Note:
- If there are two fields with the same name, the table name (alias) will be used.
- Order BY is placed after the where condition.
2) Self-connection: The connection query for the same table, which treats a table as two tables or multiple tables. eg: Displays the name of each employee of the company and the name of his superior. Consider an EMP table as two tables worker and boss
- Select Worker.ename Employee, Boss.ename boss from EMP Worker,emp boss where Worker.mgr=boss.empno
2. Subquery (nested query): A SELECT statement embedded in another SQL statement. 1) Single-line subquery: A subquery statement that returns only one row of data. 2) Multiline subquery: A subquery that returns multiple rows of data. 3) Use a subquery in the FROM clause. Description:When a subquery is used in the FROM clause, the subquery is treated as a view (temporary table) and, therefore, is also called an inline view. When you use a query in the FROM clause, you must specify an alias for the subquery.
3, paging query1) Top N: First N records.
- Select Top 5 * from emp ORDER by HireDate
- Displays information from 5th to 9th persons (by Sal High and low)
-
- Select Top 5 from EMP where empno not in (select Top 4 empno from emp ORDER BY Sal Desc) Order by Sal Desc
- Identity: Indicates that the field is growing from "1", with "1" added each time.
-
- CREATE TABLE test (TestID int primary key identity)
4. Create a new table with query results
- SELECT * into another table name from table name
- Delete duplicate records in a table
-
- SELECT DISTINCT * into #temo (new table) from table name 1
- Delete from table name 1
- Insert into table name 1 SELECT * FROM #temp
- drop table #temp
5. External connection:
- Left OUTER join: The record of the table on the left is all displayed, if there is no matching record, fill with null.
- Right outer join: The record of the table on the right is all displayed, if there is no matching record, fill with null.
Database SQL Server2012 notes (iv)-Multiple table queries, subqueries, paged queries, creating new tables and outer joins with query results
