SQL Get started Classic (iii) connection query

The previous article describes the query. This article is mainly about connection queries, which will introduce inner Join,outer join (left and right), full Join,cross join.

Connecting Gunas means merging multiple data table data into a single result set.

Retrieving matching data with an internal connection (inner join)

The connection structure syntax is as follows: SELECT <column list> from <first table> <join_type> <last table> [on <join condition& gt;]

Then the above. The manager has a new need. Ask you to check the order date for each item, and look at the following 3 requirements code:

SelectP.productid,p.name,o.unitprice,o.orderqty,o.modifieddate fromProduction.Product asPInner JoinSales.SalesOrderDetail asO onP.productid=O.productid--query to 121,317 datasetsSelectP.*, O.unitprice,o.orderqty,o.modifieddate fromProduction.Product asPInner JoinSales.SalesOrderDetail asO onP.productid=O.productid----Query to 121,317 datasets (note that the following p.* are different.) * represents all columns. P indicates that all columns of p are not queried.SelectProductid,o.unitprice,o.orderqty,o.modifieddate fromProduction.Product asPInner JoinSales.SalesOrderDetail asO onP.productid=O.productid

--(Unable to query to, run a problem: returned "message 209, Level 1, status 1 , column name 'ProductID ' not clear. "error message because SQL does not know that ProductID is the column of that table)

Read 2 data sheet data (mainly on external connections and full connections)

td>            Contact          /tr>

Order

id     &NBSP ;	name
1	test3	null
2	test4	1
3	test5	5
4	Test 6	2

Contact

Id	Name
1	Test1
2	Test2
3	Test3
4	Test4

Retrieve more data using external (outer join).

External connections are rarely used for actual development. We also need to understand external connections.

The basic syntax for external connections is similar to internal connections: SELECT <select column> from <the table, want to being the left table> <left| Right>[outer] JOIN <table you want to being the right table >[on] <join condition> OUTER can be shortened. In order to follow the ISO standardized code, I added

SELECTO.*C.*  from  Order  asOInner JoinContact asC onC.id=O.contact--inner query only 2 results. SELECTO.*C.*  from  Order  asO Left JoinContact asC onC.id=O.contact--left query only 4 results, there are 2 results c.id and c.name are null, the data does not match to so null. SELECTO.*C.*  from  Order  asO Right JoinContact asC onC.id=O.contact--rightquery only 5 results, there are 3 results O. The ID and o.name,o.contact are null, and the data does not match to null.

External connections primarily retrieve more orphaned data

Fully connected (full join)

External connections are rarely used, as long as you understand the concept. As the name implies, the full connection is the left and right union queries. Returns all data for 2 tables. No match to value is null instead. Look at the external connection SQL statement first

SELECT o.*, C.*fromOrderas fullJOINas   on c.id=o.contact-- a total of 7 data (supposedly, it should be 5+4=9 data, because there are 2 data in the middle match, all 7)

Cross Join

A cross connection is also called a Cartesian connection. This is used less and is used primarily to test large amounts of data. Make an analogy. There are 2 of tables. One is to save a last name with 100 last names, and a saved name with 1000 names. I want 100,000 names. The cross connection is used. Continue to the table above.

SELECT o.*, C.*fromOrderas crossJOIN  as C  -- 20 data will be generated. The amount of data is a bit too large to be posted. 

Early syntax in SQL2000. Although it is not in use now. Now the sql2012 version is not compatible. Remember early grammar when you meet.

Internal connection: SELECT * from Order,contact where order.contact=contact.id; instead of inner join and where instead of on.

External connection: SELECT * from Order,contact where order.contact*=contact.id; replace (left|right) join and where instead of on, back condition (*= left connection, =* right connection)

Cross-connect: SELECT *from order,contact; remove crosses JOIN to add a keyword

Union (Union)

Then the above table: relatively simple, that is, 2 table data merged into one table (note that 2 tables of data structures and columns to union)

Select id,name FORM Contact UNION (all) Select Order -- All is an optional parameter. If no all,2 result set has coincident data, discard one, plus all is not discarded. 

SQL Get started Classic (iii) connection query