There are several differences in SQL queries.

Recently read a few SQL query article to do a summary oh, probably précis-writers as follows:

The essence of SQL query is that you get a subset of the data from the database, you can take a subset of the columns first, and then take a subset of the rows that match the criteria.

1, single-table query:

SELECT [Name], [GroupName] from [AdventureWorks]. [HumanResources]. [Department] WHERE departmentid>1 and Departmentid<3 or departmentid>5 and departmentid<7

2. Multi-table connection query: connection (join) is an important means of connecting multiple tables into a single table.

3. The Cartesian product of Cartesian product is implemented in SQL as a cross join. All connections are made to the temporary Cartesian product table, the Cartesian product is a concept in relational algebra, representing two tables of each row of data any combination, the two tables connected to the Cartesian product (cross connection)

4, internal connection If you understand the steps in step, the inner connection can be considered as a cross-connection to two tables, and then by adding the constraints (SQL in the keyword on) to eliminate the sub-qualifying rows of the subset , The result is an inner connection. The above figure, if I add a limit condition

For the first two tables, if the query statement is as follows: SELECT * FROM [Class] c inner JOIN [Student] s on C.classid=s.studentclassid

5. The SQL query statement using the relational calculus method is as follows,SELECT *from [Class] C, [Student] s where C.classid=s.studentclassid

6. An outer join allows a party to join the table, or both parties are not required to adhere to the connection restrictions behind on. Change the inner join in the query statement above to the left outer join:

7. The concept of Right outer connection right outer connection and left outer connection is the same, but the order is different, for the above query statement, can also be changed to:SELECT s.studentname,c.classname from [fordemo].[ DBO]. [Class] C Right outer join [fordemo].[ DBO]. [Student] s on S.studentclassid=c.classid

8. The full outer join is to output at least once per row of the left and right tables, connected by the keyword "full outer join", and can be seen as a combination of left and right outer joins.

9, Self -connection: Self-connected is a special connection, is the physical same but logically different tables to connect the way.

10, Sub -query: Sub-query according to the type of data returned by the subquery, can be divided into three kinds, respectively: 1 Returns a data table (table) 2 Returns a column value (column) 3 returns a single value (Scalar)

Typically: A subquery is used as a data source using SELECT P.productid, P.name, P.productnumber, m.name as Productmodelnamefrom Production.produ CT as P INNER JOIN (SELECT Name, ProductModelID from Production.ProductModel) as MON p.productmodelid = M.productmodel Id

B subqueries are used as selection criteria SELECT [Firstname],[middlename], [LastName] from [AdventureWorks]. [Person]. [Contact] WHERE ContactID in (SELECT EmployeeID from [adventureworks].[ HumanResources]. [Employee]

WHERE sickleavehours>68)

The queries referred to in the C correlation subquery and the EXISTS keyword are irrelevant subqueries (uncorrelated subquery), The subquery also has a kind of very important query is related subquery (correlated subquery), also known as a repeating subquery, such as, or the above query, with the relevant subquery to write:

The D subquery is used as a computed column when the subquery is used as a computed column , returning only a single value (Scalar). Used as a computed column after the SELECT statement. Also divided into correlated sub-queries and unrelated subqueries

11. types of data set operations

A . A∪B uses Union to implement the union in T-SQL to implement the a∪b operation, in fact the Union has two versions , respectively: Union Union represents the relationship of A∪b, and retains the only one when it encounters the same row in two data sets:

B . A∩B, using INTERSECT to implement T-SQL provides INTERSECT keywords to implement A∩B relationships

C. A∩B, using INTERSECT to implement T-SQL provides INTERSECT keywords to implement A∩B relationships

There are several differences in SQL queries.